MySQL query takes 20 secs when ordered by PK but 0.0006 ordered by timestamp?

[Andy-H]

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

[Illusion]

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 .

[Andy-H]

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?

 

 

 

[Illusion]

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  ?

 

[Andy-H]

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. :/

[Illusion]

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.

[Andy-H]

The first image is for the order by id (20 second) query, the second is order by timestamp 0.000x query.

[mrMarcus]

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.

[Andy-H]

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.

[mrMarcus]

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.

[Andy-H]

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.