4

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.

Abdullah Khan
  • 2,384
  • 2
  • 22
  • 32

2 Answers2

4

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

Galz
  • 6,713
  • 4
  • 33
  • 39
  • Galz, your definition makes sense, but, just out of curiosity, how did you figure it out? If I search mySql and "Clever Key", there are 462 results; all of them a derivative of the linked list in this article. Additionally, if I do the same search and exclude all links that have the text "Use a clever key and ORDER BY instead of MAX", I get 6 non-related results. Granted, Google is not all powerful with its terms (yet), but just curious how the "clever key" term got coined. – ray Mar 29 '11 at 20:37
  • 1
    @ray023 - clever key is not a coined term. Akay could have asked about an efficient key or a good key just the same:) I found the article above a shirt while ago and it came to my mind when reading this question - search google for "mysql order by optimization", that article would be the first result. – Galz Mar 29 '11 at 20:46
1

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.

Community
  • 1
  • 1
ray
  • 8,521
  • 7
  • 44
  • 58