Quickies, by Andrea Olivato

Max ID from Mysql - Speed tests

I know three methods able to retrieve the max ID (primary key with autoincrement) of a table and I wanted to know which was faster so I run some tests.

SELECT Auto_increment FROM information_schema.tables WHERE table_name="MYDB" AND table_schema="MYTABLE" LIMIT 1;

SELECT MAX(id) From MYDB.MYTABLE LIMIT 1;

SELECT id FROM  MYDB.MYTABLE ORDER BY id DESC LIMIT 1

Before giving you the results I’ll give you some info on the testing environment:

MYTABLE got 9,383,795 lines, and is 0,9 GiB

information_schema.tables views contains 17,095 records

Here are the results

SELECT Auto_increment FROM information_schema.tables WHERE table_name="MYDB" AND table_schema="MYTABLE" LIMIT 1;
-- showing rows 0 - 0 (1 total, Query took 0.0426 sec) => THIS SUCKS

SELECT MAX(id) From MYDB.MYTABLE LIMIT 1;
-- showing rows 0 - 0 (1 total, Query took 0.0087 sec) => QUITE GOOD

SELECT id FROM  MYDB.MYTABLE ORDER BY id DESC LIMIT 1
-- showing rows 0 - 0 (1 total, Query took 0.0003 sec) => THAT'S WHAT I MEAN

Results might be obvious for someone… The first one sucks so much because information_schema.tables is a view reading from all other db & tables. I wouldn’t recommend this on full production website :)

I personally wasn’t sure about the second ones performance, but reasoning about that after seeing the results, the MAX function needs to compare all ids, while ordering and taking the first should be logically easier for the calculator…