super slow relational query -- because: math

[michaellunsford]

The problem is `subscriberid` in one table is a negative number, and positive in the other table. To get a match, MySQL has to do the math. (see first WHERE clause). Is there a better way to write this query?

SELECT
  `name`,
  `email`,
  `subscribe_date`,
FROM
  `subscriber`,
  `list`
WHERE
  (`subscriber`.`subscriberid` + `list`.`subscriberid` = 0)
  AND `unsubscribe_date` = '0000-00-00 00:00:00'
  AND `confirmed` = 1
LIMIT 0, 20

This query, limited to 20 results, took 45 seconds to run.

 

Note: I didn't design it -- just need the data, and can't break anything to get it.

[Barand]

I'd start by using an explicit join syntax and ensure that subscriberid was indexed in both tables and an index on the date field

SELECT
  `name`,
  `email`,
  `subscribe_date`,
FROM
  `subscriber`
  INNER JOIN `list` ON `subscriber`.`subscriberid` = -`list`.`subscriberid`
WHERE
  `unsubscribe_date` = '0000-00-00 00:00:00'
  AND `confirmed` = 1
LIMIT 0, 20

[gizmola]

Any time you try and relate tables using a computed value, no indexes can be used.  Index matching is very simple stuff, a must equal b.  No doubt you are table scanning the tables, and your time to run is a function of that. 

 

Of course you can and should do "EXPLAIN EXTENDED query" to validate statements like the one I'm making, but having two tables related by an integer key, and having one of those values be negative, is just comically wrong.  I believe there are even small datatype mismatch issues like whether or not the column was signed vs unsigned that can cause problems.

[michaellunsford]

having two tables related by an integer key, and having one of those values be negative, is just comically wrong.

 

Tell me about it. It's nuts! Who does that?

 

Barand, that inner join did the trick. The query was nearly instantaneous! Now to see how 500 or 1000 records will do.

[gizmola]

Getting the join syntax right was certainly important, however i bet it's still table scanning.  Did you do the EXPLAIN EXTENDED on the query?

 

If it's a small data set it won't matter that it's table scanning, but it does put a lot of stress on the database, if these types of queries are happening frequently, and of course over time, if the dataset becomes large, performance degrades.

 

If this is just a "once and a while" query, then I wouldn't worry about it.

[michaellunsford]

If this is just a "once and a while" query, then I wouldn't worry about it.

 

It's not just "once in a while" this is a "just once" query. The data is being offloaded for a migration. By the way, all records (about 5,000) came through in under a second.

 

For grins, I ran the EXPLAIN EXTENDED on it, but it looks like part of the answer was truncated by phpMyAdmin. I could run it from the command-line if you'd like. Also, I did check SHOW INDEX on both tables before running the query, and every field (including ones not called in the query) was indexed.

 

id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE list ref PRIMARY PRIMARY 4 const 6422 100.00 Using index 1 SIMPLE subscriber eq_ref PRIMARY,idx_subscriber_confirmed,idx_s... PRIMARY 4 func 1 100.00 Using where

 

edit: looks like the forum editor dropped the table the above was wrapped in :-/

Edited January 14, 2014 by michaellunsford