Let's say I have a mysql table defined like this:
create table test_table(
id int(10) unsigned auto_increment primary key
/*, other attributes...*/
);
And given that table I wanna fetch the last record from it like this:
select * from test_table order by id desc limit 1;
It works, but it feels a bit sketchy, what is its complexity? Is it O(log(n)) since "limit" and "order by" are executed after the select?
Is there a better way to select the last record from an auto incremented table?
I think I figured it out.
My initial question was linked to "Select & Limit", but really this applies to all queries.
MySQL provides the "analyze" keyword. You can invoke it on your terminal an then execute your query; It will output some metadata regarding the details of the execution.
Here's an example using the table in my question (I change its name to "comment" and its PK to "commentid" to give it some context):
analyze
select * from comment order by commentid desc limit 1;
And the following is the output:
"rows" tells you how many rows the query iterated and "r_rows" are the result rows.
This is what I was looking for.
I was living under the impression that somehow the "limit" keyword would optimize the query. It doesn't.
On the other hand, you can also use MAX() to get the last row
analyze
select * from comment where commentid=(select max(commentid) from comment);
The primary query iterates just 1 row obviously, but the subquery should be the most complex select of the two, so I analyzed it:
analyze
select max(commentid) from comment;
gave me:
This doesn't tell me much, except for the "extra" description, which says: "Select tables optimized away".
I looked that up and it's an already answered question on stack
From what I've gathered so far that description means the MAX doesn't actually count the rows of your table, instead it uses a stored value which is managed by the sql engine.
It only works if the column has "auto_increment".
The accepted answer also says it only works on MyISAM tables, but I'm running these tests on a InnoDB table, and the optimization seems to be working.
Here are the details:
SELECT PLUGIN_NAME, PLUGIN_VERSION, PLUGIN_TYPE_VERSION, PLUGIN_LIBRARY, PLUGIN_LIBRARY_VERSION, PLUGIN_AUTHOR
FROM information_schema.PLUGINS
WHERE PLUGIN_NAME = 'innodb';
PS: You might be wondering if doing this:
ALTER TABLE comment AUTO_INCREMENT = 999;
messes up the optimization.
The answer is no, it doesn't, setting the AUTO_INCREMENT to a certain value only affects the next entry.
Try it yourself, modify the AUTO_INCREMENT value and then run
select max(commentid) from comment;
you will still get the correct value.
You can get desired output with this approach as well.
SELECT * FROM test_table where id=(select max(id) from test_table);
Hope, this will help you.
