Mysql: Best Practice for Retrieving Recent Entries

Question

I have a hot question about mysql ...

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

At this point you want to answer:

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, which is OK, mysql reads the index from the end and changes 10 elements, the runtime is negligible. When connected, the runtime becomes dependent on the size of the table and in many cases is not insignificant, an explanation indicating that mysql: "Use where: use index ; use temporary; use filesort "

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

"You are joining many tables, and the columns in ORDER BY are not all from the first mutable table, which is used to retrieve the rows. (This is the first table in the EXPLAIN output, which does not have the type of joining constants.)"

explaining why MySQL cannot use the index to solve my ORDER BY, preferring huge file sorting ...

My question is: is it OK to use ORDER BY ... LIMIT 10 to get the latest items? Do you really do this by choosing the last 10 cards in an increasing ordered deck of cards? Personally, I just pick 10 from the bottom ...

I tried many possibilities, but it all ended up coming to the conclusion that I really quickly asked for the first 10 elements and slowly asked for the last 10 reasons for the ORDER BY clause.

Can I quickly select "Select last 10"? Where am I mistaken?

Answer 1

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

Answer 2

I have problems reproducing your situation. I use ASC or DESC with a huge_table / another_table mask, my EXPLAIN and runtime all show approximately N rows read and a logical connection. What version of MySQL are you using?

Also, from the EXPLAIN doc it is indicated that Using index indicates

Column information is retrieved from the table using only the information in the index tree without the need to make an additional attempt to read the actual row

which is not consistent with the fact that you are doing SELECT * unless you have an index that spans your entire table.

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

Answer 3

When joining, you are now restricting the rows to "some_interesting_property", and the identifier in your huge table can no longer be sequential ... Try an index on another_table (some_interesting_property, id), as well as a huge table (another_table_id, id) and see if your EXPLAIN gives you the best hints.