Understanding Some MySQL Optimization Tips

Question

Hey, I was looking for some ways to make my MySQL queries more optimized, I bought some in practice, but for some I want to understand why they are recommended.

For example, not using “LIMIT 10000.10,” since I found out that MySQL reads 10010 rows, throws 10000 and returns 10.

Now I want to know:

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

"Use smart key and ORDER BY instead of MAX"

• What is a smart key?

• Which MySQL execution path takes to calculate MAX, which was recommended to avoid it.

Thanks.

Answer 1

A smart key is a key that will allow mysql to read rows according to the desired order, therefore it does not use filesort or temporary tables for sorting.

It does not have to be a primary key. To find out 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

Answer 2

I am going to take a hit in question 1 and say that the “smart key” is the primary key on the table that matters when ordering a particular method. (for example, a hotel database with a table that keeps track of when guests check in. Make the primary key of the TimeStamp registration table. Then, when you need to know the most recent check-in, you can order the main key in descending order). I believe this is faster than a table that uses an automatically increasing integer as a primary key and a timestamp field.

You can find your answer to question 2 at fooobar.com/questions/88054 / .... It should give you an idea of ​​the execution path, but does not necessarily compare it with the smart key provided in your link.