Mysql: Best practice to get last records

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 n开发者_JAVA百科atural 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 ?

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

With the join you're now restricting rows to "some_interesting_property" and the ID's in your huge_table may no longer be consecutive... Try an index on another_table (some_interesting_property, id) and also huge_table (another_table_id, id) and see if your EXPLAIN gives you better hints.

I'm having trouble reproducing your situation. Whether I use ASC or DESC with my huge_table/another_table mock up, my EXPLAINs and execution time all show approx N rows read and a logical join. Which version of MySQL are you using?

Also, from the EXPLAIN doc, it states that Using index indicates

The column information is retrieved from the table using only information in the index tree without having to do an additional seek to read the actual row

which doesn't correspond with the fact you're doing a SELECT *, unless you have an index which covers your whole table.

Perhaps you should show your schema, including indexes, and the EXPLAIN output.