Which mysql select is better/faster?

Question

Is there a preferred way? There are currently 1640 rows in the game table to go through and will be adding about 1200 every year.

SELECT `date` FROM `game` WHERE `date`<'2009-11-09' ORDER BY `date` DESC LIMIT 1;

0.0004 seconds

SELECT MAX(`date`) AS `date` FROM `game` WHERE `date`<'2009-11-09' LIMIT 1;

0.0006 seconds

The speeds were for the first time this ran. Each time after was 0.0002 for each.

mySQL:
Server: Localhost via UNIX socket
Server version: 5.1.37

PHP (shouldn't be relevant):
5.x

Answer 1

With those numbers of rows and the simplicity of the queries, it should not matter. You also do not need the limit on the second query. Just choose whichever one is easier for you to understand.

Answer 2

In second query you don't have to add LIMIT 1. It will always return 1 row. I would say that second query is more readable and you should use. But I agree with @jle. You have very small database and it really does not affect performance very much.

Answer 3

Apply MySQL EXPLAIN and check the query plans. The second example may well have a cleaner plan, which is nice as it looks cleaner to my eye. (Once you remove the limit.)

A noddy test locally shows (no indexes).

Query 1:

EXPLAIN
SELECT  datex
FROM    TABLE_X x
WHERE datex < "2009-10-20"
ORDER BY datex DESC
LIMIT 1

Plan

id select_type table type possible_keys key  key_len ref  rows Extra
1  SIMPLE      x     ALL  NULL          NULL NULL    NULL 2    Using where; Using filesort

Query 2:

EXPLAIN
SELECT  MAX( datex )
FROM    TABLE_X x
WHERE datex < "2009-10-20"

Plan

id  select_type table type  possible_keys   key     key_len ref   rows Extra
1   SIMPLE      x     ALL   NULL            NULL    NULL    NULL  2    Using where

Answer 4

You can look at what the plan is (ie how mysql does the work) using explain - http://dev.mysql.com/doc/refman/5.0/en/explain.html

I imagine they are pretty much identical, but that's the best way to check (it may depend on indices etc).

But I have to ask - do you really need to worry about this? It's not exactly slow, and the table isn't growing at a huge rate.

Finally, you don't need the second "limit".

Answer 5

I would also want to add to all the other responses:

try to add a million records. Best way to know for sure.

Answer 6

SELECT MAX(date) AS date FROM game WHERE date < '2009-11-09' LIMIT 1;

...takes longer because it's running the MAX first, and LIMIT 1 afterwards. You could think of it like:

SELECT x.date
  FROM (SELECT MAX(t.date) 'date'
          FROM GAME t 
         WHERE t.date < '2009-11-09') x
 LIMIT 1;

It's a poor comparison against:

SELECT date FROM game WHERE date < '2009-11-09' ORDER BY date DESC LIMIT 1;

...because they're return different results.

Which is better/faster?

It's an apples to oranges comparison.
The MAX query will return the highest value based on the criteria, and return the first record at the top the the query arbitrarily because there isn't an ORDER BY clause. The query that doesn't use MAX will return the first arbitrary record - it could be the highest, the lowest, somewhere between and it likely would be a different record every time you run the query. There's the same risk with the MAX query, but the margin of error is much smaller because you'd have to ties (multiples with the same max value).