Andy-H Posted May 23, 2012 Share Posted May 23, 2012 We have a database of locations, this query executes fine when run on other devices, but this phone number has 65,000 results, and it takes around 20 seconds to execute when ordered by id (which is a primary key so must be indexed, right?) Showing rows 0 - 9 (10 total, Query took 20.2472 sec) SELECT * FROM `newlocations` WHERE device = '077********' ORDER BY id DESC LIMIT 10 But when ordered by thetimestamp (unix timestamp, also indexed), it takes < 0.001 second Showing rows 0 - 9 (10 total, Query took 0.0008 sec) SELECT * FROM `newlocations` WHERE device = '077********' ORDER BY thetimestamp DESC LIMIT 10 Anyone got any idea what could be causing this to happen? As I say, with other phone numbers the query executes reasonably quickly Showing rows 0 - 9 (10 total, Query took 0.3845 sec) SELECT * FROM `newlocations` WHERE device = '077********' ORDER BY id DESC LIMIT 10 Quote Link to comment https://forums.phpfreaks.com/topic/262976-mysql-query-takes-20-secs-when-ordered-by-pk-but-00006-ordered-by-timestamp/ Share on other sites More sharing options...
Illusion Posted May 23, 2012 Share Posted May 23, 2012 There are some cases where mysql can't use indexes for sorting..one that case is when key in where clause in not same as that of order by clause. You can prepend your query with EXPLAIN or EXPLAIN EXTENDED to know whether indexes are used for order by optimization or not . Quote Link to comment https://forums.phpfreaks.com/topic/262976-mysql-query-takes-20-secs-when-ordered-by-pk-but-00006-ordered-by-timestamp/#findComment-1347882 Share on other sites More sharing options...
Andy-H Posted May 23, 2012 Author Share Posted May 23, 2012 Cheers, I've never really understood what this information means and can't find any decent resources to help me figure it out (maybe an opportunity for a PHPFreaks tutorial?), could you shed any light on it for me? Quote Link to comment https://forums.phpfreaks.com/topic/262976-mysql-query-takes-20-secs-when-ordered-by-pk-but-00006-ordered-by-timestamp/#findComment-1347884 Share on other sites More sharing options...
Illusion Posted May 23, 2012 Share Posted May 23, 2012 I am no expert at that... You can find more information here http://dev.mysql.com/doc/refman/5.6/en/explain-output.html From the output .... it identified possible keys( latestlocation) to use as index and it is not suitable to filter the rows ..it chosen primary key as index . Can you post the EXPLAIN output for other query where you are ordering by timestamp ? Quote Link to comment https://forums.phpfreaks.com/topic/262976-mysql-query-takes-20-secs-when-ordered-by-pk-but-00006-ordered-by-timestamp/#findComment-1347899 Share on other sites More sharing options...
Andy-H Posted May 23, 2012 Author Share Posted May 23, 2012 That's the one, the query seems to run faster now that the data is less fragmented, I don't understand why this wasn't affecting the order by timestamp query tho. :/ Quote Link to comment https://forums.phpfreaks.com/topic/262976-mysql-query-takes-20-secs-when-ordered-by-pk-but-00006-ordered-by-timestamp/#findComment-1347913 Share on other sites More sharing options...
Illusion Posted May 23, 2012 Share Posted May 23, 2012 It is quite obvious that query optimizer chosen a wrong plan for one of the query . You can use Force Index or Force Key to make mysql to pick your plan. which image is for which query is still confusing though... you have not named them properly. Quote Link to comment https://forums.phpfreaks.com/topic/262976-mysql-query-takes-20-secs-when-ordered-by-pk-but-00006-ordered-by-timestamp/#findComment-1347922 Share on other sites More sharing options...
Andy-H Posted May 23, 2012 Author Share Posted May 23, 2012 The first image is for the order by id (20 second) query, the second is order by timestamp 0.000x query. Quote Link to comment https://forums.phpfreaks.com/topic/262976-mysql-query-takes-20-secs-when-ordered-by-pk-but-00006-ordered-by-timestamp/#findComment-1347932 Share on other sites More sharing options...
mrMarcus Posted May 23, 2012 Share Posted May 23, 2012 I would suggest putting an INDEX on (`id`, `device`). I'm assuming you do not currently have an INDEX on `device`? And perhaps you could better normalize your database. You say that this one device, alone, has ~65K results in a single table? Why is that? What's different about each record for that particular device? What is the `latestlocation` index/key made up of? When you hit a CONST as the 'ref', you can *typically* expect fast execution and results. Your initial query was not able to make that same connection. Could you please run: SHOW INDEXES FROM `newlocations`; Within either phpMyAdmin or from shell, and display the results here. Quote Link to comment https://forums.phpfreaks.com/topic/262976-mysql-query-takes-20-secs-when-ordered-by-pk-but-00006-ordered-by-timestamp/#findComment-1348024 Share on other sites More sharing options...
Andy-H Posted May 23, 2012 Author Share Posted May 23, 2012 We are in the process of re-designing the whole database, I didn't design the current database, no there is no index on ID / device. My boss just asked me if I knew why it was happening then I got interested, I don't think it's a query used anywhere in any application as we normally need locations in chronological order. Quote Link to comment https://forums.phpfreaks.com/topic/262976-mysql-query-takes-20-secs-when-ordered-by-pk-but-00006-ordered-by-timestamp/#findComment-1348046 Share on other sites More sharing options...
mrMarcus Posted May 23, 2012 Share Posted May 23, 2012 The higher the cardinality, the better. You can see - and use this for future reference - that device has a very low cardinality compared to thetimestamp. People often make the mistake of placing an index on every column thinking that will speed up your system queries, but it can also have a negative impact especially if the optimizer chooses to use that index. When you have a low cardinality, that typically means that you could better normalize your database. As I asked earlier, having ~65K records of the same device in your table seems quite off. Quote Link to comment https://forums.phpfreaks.com/topic/262976-mysql-query-takes-20-secs-when-ordered-by-pk-but-00006-ordered-by-timestamp/#findComment-1348051 Share on other sites More sharing options...
Andy-H Posted May 23, 2012 Author Share Posted May 23, 2012 There is like 1 repetitive field in there for location type, plus it has input1, input2, input3, input4, input5, input6 and inputi, but as I said, were re-designing the database - the current one was designed before I worked there. Quote Link to comment https://forums.phpfreaks.com/topic/262976-mysql-query-takes-20-secs-when-ordered-by-pk-but-00006-ordered-by-timestamp/#findComment-1348097 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.