Understanding some MySQL Optimization tips

Hey, I was looking up some ways to make my MySQL queries more optimized, I bought some into practice but for some I开发者_运维百科 wish to understand why they are recommended.

For example not using "LIMIT 10000,10" as I learned that MySQL reads 10010 rows, throws away the 10000 and returns the 10.

Now what I want to know is:

Referring to this article: http://forge.mysql.com/wiki/Top10SQLPerformanceTips

"Use a clever key and ORDER BY instead of MAX"

1. What is a "clever key" ?

2. What execution path does MySQL take to calculate MAX that it has been recommended to avoid it.

Thank you.

A clever key is a key that will allow the mysql to read the rows according to the wanted order, thus not using filesort or temporary tables for the sorting.

It does not have to be a primary key. To learn when you can use such a key for sorting read this very useful article: http://dev.mysql.com/doc/refman/5.0/en/order-by-optimization.html

I'm going to take a stab at question 1 and say a "clever key" is a primary key on a table which has value when ordered a certain way. (e.g. A hotel database with a table that tracks when guests check in. Make the primary key of the table the TimeStamp of the check-in. Then, when you need to know the most recent check-ins, you can order by the primary key in descending order). I suppose that is faster than having a table that uses an auto-incrementing integer as the primary key and a Timestamp field.

You might find your answer to Question 2 in this stackoverflow discussion. It should give you an idea of the execution path, but doesn't necessarily compare it the clever key mentioned in your link.