michaellunsford Posted January 14, 2014 Share Posted January 14, 2014 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. Quote Link to comment https://forums.phpfreaks.com/topic/285365-super-slow-relational-query-because-math/ Share on other sites More sharing options...
Solution Barand Posted January 14, 2014 Solution Share Posted January 14, 2014 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 Quote Link to comment https://forums.phpfreaks.com/topic/285365-super-slow-relational-query-because-math/#findComment-1465229 Share on other sites More sharing options...
gizmola Posted January 14, 2014 Share Posted January 14, 2014 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. Quote Link to comment https://forums.phpfreaks.com/topic/285365-super-slow-relational-query-because-math/#findComment-1465230 Share on other sites More sharing options...
michaellunsford Posted January 14, 2014 Author Share Posted January 14, 2014 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. Quote Link to comment https://forums.phpfreaks.com/topic/285365-super-slow-relational-query-because-math/#findComment-1465232 Share on other sites More sharing options...
gizmola Posted January 14, 2014 Share Posted January 14, 2014 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. Quote Link to comment https://forums.phpfreaks.com/topic/285365-super-slow-relational-query-because-math/#findComment-1465234 Share on other sites More sharing options...
michaellunsford Posted January 14, 2014 Author Share Posted January 14, 2014 (edited) 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 Quote Link to comment https://forums.phpfreaks.com/topic/285365-super-slow-relational-query-because-math/#findComment-1465242 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.