Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>The first thing that jumps out at me as the source of all the trouble:</p> <blockquote> <p>The PHP app's table is an E-A-V style table...</p> </blockquote> <p>Trying to convert data in EAV format into conventional relational format on the fly using SQL is bound to be awkward and inefficient. So don't try to smash it into a conventional column-per-attribute format. The following query returns multiple rows per subscriber, one row per EAV attribute:</p> <pre><code>SELECT ls.subscriberid AS id, SUBSTRING_INDEX(l.name, _utf8'_', 1) AS user_id, COALESCE(ls.emailaddress, _utf8'') AS email_address, s.fieldid, s.data FROM list_subscribers ls JOIN lists l ON (ls.listid = l.listid) LEFT JOIN subscribers_data s ON (ls.subscriberid = s.subscriberid AND s.fieldid IN (2,3,34,35,36,81,100,154) WHERE SUBSTRING_INDEX(l.name, _utf8'_', 1) REGEXP _utf8'[[:digit:]]+' </code></pre> <p>This eliminates the <code>GROUP BY</code> which is not optimized well in MySQL -- it usually incurs a temporary table which kills performance. </p> <pre><code>id user_id email_address fieldid data 1 1 jdoe@example.com 2 John 1 1 jdoe@example.com 3 Doe 1 1 jdoe@example.com 81 5551234567 </code></pre> <p>But you'll have to sort out the EAV attributes in application code. That is, you can't seamlessly use ActiveRecord in this case. Sorry about that, but that's one of the <strong>disadvantages of using a non-relational design like EAV.</strong></p> <p>The next thing that I notice is the killer string manipulation (even after I've simplified it with <code>SUBSTRING_INDEX()</code>). When you're picking substrings out of a column, this says you me that you've overloaded one column with two distinct pieces of information. One is the <code>name</code> and the other is some kind of list-type attribute that you would use to filter the query. <strong>Store one piece of information in each column.</strong></p> <p>You should add a column for this attribute, and index it. Then the <code>WHERE</code> clause can utilize the index:</p> <pre><code>SELECT ls.subscriberid AS id, SUBSTRING_INDEX(l.name, _utf8'_', 1) AS user_id, COALESCE(ls.emailaddress, _utf8'') AS email_address, s.fieldid, s.data FROM list_subscribers ls JOIN lists l ON (ls.listid = l.listid) LEFT JOIN subscribers_data s ON (ls.subscriberid = s.subscriberid AND s.fieldid IN (2,3,34,35,36,81,100,154) WHERE l.list_name_contains_digits = 1; </code></pre> <p>Also, you should <strong>always analyze an SQL query with <a href="http://dev.mysql.com/doc/refman/5.1/en/using-explain.html" rel="nofollow noreferrer"><code>EXPLAIN</code></a></strong> if it's important for them to have good performance. There's an analogous feature in MS SQL Server, so you should be accustomed to the concept, but the MySQL terminology may be different. </p> <p>You'll have to read the documentation to learn how to interpret the <code>EXPLAIN</code> report in MySQL, there's too much info to describe here.</p> <hr> <p>Re your additional info: Yes, I understand you can't do away with the EAV table structure. Can you create an <em>additional</em> table? Then you can load the EAV data into it:</p> <pre><code>CREATE TABLE subscriber_mirror ( subscriberid INT PRIMARY KEY, first_name VARCHAR(100), last_name VARCHAR(100), first_name2 VARCHAR(100), last_name2 VARCHAR(100), mobile_phone VARCHAR(100), sms_only VARCHAR(100), mobile_carrier VARCHAR(100) ); INSERT INTO subscriber_mirror (subscriberid) SELECT DISTINCT subscriberid FROM list_subscribers; UPDATE subscriber_data s JOIN subscriber_mirror m USING (subscriberid) SET m.first_name = IF(s.fieldid = 2, s.data, m.first_name), m.last_name = IF(s.fieldid = 3, s.data, m.last_name), m.first_name2 = IF(s.fieldid = 35, s.data, m.first_name2), m.last_name2 = IF(s.fieldid = 36, s.data, m.last_name2), m.mobile_phone = IF(s.fieldid = 81, s.data, m.mobile_phone), m.sms_only = IF(s.fieldid = 100, s.data, m.sms_only), m.mobile_carrer = IF(s.fieldid = 34, s.data, m.mobile_carrier); </code></pre> <p>This will take a while, but you only need to do it when you get a new data update from the vendor. Subsequently you can query <code>subscriber_mirror</code> in a much more conventional SQL query:</p> <pre><code>SELECT ls.subscriberid AS id, l.name+0 AS user_id, COALESCE(s.first_name, s.first_name2) AS first_name, COALESCE(s.last_name, s.last_name2) AS last_name, COALESCE(ls.email_address, '') AS email_address), COALESCE(s.mobile_phone, '') AS mobile_phone, COALESCE(s.sms_only, '') AS sms_only, COALESCE(s.mobile_carrier, '') AS mobile_carrier FROM lists l JOIN list_subscribers USING (listid) JOIN subscriber_mirror s USING (subscriberid) WHERE l.name+0 &gt; 0 </code></pre> <p>As for the userid that's embedded in the <code>l.name</code> column, if the digits are the leading characters in the column value, MySQL allows you to convert to an integer value much more easily:</p> <p>An expression like <code>'123_bill'+0</code> yields an integer value of 123. An expression like <code>'bill_123'+0</code> has no digits at the beginning, so it yields an integer value of 0.</p>
    singulars
    1. This table or related slice is empty.
    1. POOptimizing a strange MySQL Query
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USBill Karwin
    1. USBill Karwin
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. COUnfortunately we're stuck with this application since we use it for managing each user's contact lists (it's a commercial app) and the designers of said app decided to use EAV.
      singulars
      1. PO
      1. USWayne Molina
    2. COAlso since the app gets regular updates from it's vendor and we're required to upgrade to receive support, I can't really add extra columns to their tables in case it breaks anything. The regexp is there to ensure that the list names contain a digit, which is extracted out into user ID so Rails knows which user has which contact list.
      singulars
      1. PO
      1. USWayne Molina
    3. COThe only problem with your second solution is the subscribers_data table is updated almost constantly since the third party tool allows users to sign up for mailings. So there could be hundreds of new users on a daily basis.
      singulars
      1. PO
      1. USWayne Molina
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload