Mysql: Best practice to get last records

Question

Hi there ! I got a spicy question about mysql...

The idea here is to select the n last records from a table, filtering by a property, (possibly from another table). That simple.

At this point you wanna reply :

let n = 10

SELECT *
  FROM huge_table
  JOIN another_table
       ON another_table.id = huge_table.another_table_id
       AND another_table.some_interesting_property
  ORDER BY huge_table.id DESC
  LIMIT 10

Without the JOIN that's OK, mysql reads the index from the end and trow me 10 items, execution time is negligible With the join, the execution time become dependent of the size of the table and in many case not negligible, the explain stating that mysql is : "Using where; Using index; Using temporary; Using filesort"

MySQL documentation (http://dev.mysql.com/doc/refman/5.1/en/order-by-optimization.html) states that :

"You are joining many tables, and the columns in the ORDER BY are not all from the first nonconstant table that is used to retrieve rows. (This is the first table in the EXPLAIN output that does not have a const join type.)"

explaining why MySQL can't use index to resolve my ORDER BY prefering a huge file sort ...

My question is : Is it natural to use ORDER BY ... LIMIT 10 to get last items ? Do you really do it while picking last 10 cards in an ascending ordered card deck ? Personally i just pick 10 from the bottom ...

I tried many possibilities but all ended giving the conclusion that i'ts really fast to query 10 first elements and slow to query 10 last cause of the ORDER BY clause.

Can a "Select last 10" really be fast ? Where i am wrong ?

Answer 1

Nice question, I think you should make order by column i.e., id a DESC index.
That should do the trick.
http://dev.mysql.com/doc/refman/5.0/en/create-index.html