MySQL Forums
Forum List  »  InnoDB

Re: Does LIMIT 1 without ORDER BY ID bring the record with minimum ID?
Posted by: Rick James
Date: May 03, 2012 09:24PM

Technically you must include the "ORDER BY id". What is the advantage of not doing so?

Possibly, if the InnoDB table were PARTITIONed, you could get the lowest id from some partition. This might not be MIN(id) over the whole table.

As I said over 3 years ago, it is "not something to depend on".

Here are some more examples (InnoDB only):

-- SELECT * FROM TABLE LIMIT 1; -- The query optimizer will simply start a table scan, stopping after 1 row.
-- SELECT id FROM TABLE LIMIT 1; -- The query optimizer will notice that id is in the PRIMARY KEY, so it will do an "index" scan. But, since an InnoDB PK is clustered with the data, this will still read the smallest id.
-- SELECT foo FROM TABLE LIMIT 1; and assume INDEX(foo) -- The query optimizer will do an index scan and give you the lowest `foo`, not the lowest id.
-- SELECT id, foo FROM TABLE LIMIT 1; and assume INDEX(foo) -- Ditto, since every secondary index implicitly includes the PK. (Are you confused yet?)
-- SELECT foo, bar FROM TABLE LIMIT 1; and assume INDEX(foo) -- Probably lowest id. (table scan)
-- SELECT foo, bar FROM TABLE LIMIT 1; and assume INDEX(bar, foo) -- Probably lowest foo. (Index scan)

The query optimizer asks "what is the cheapest way to get the columns needed for the SELECT. An INDEX is preferred (usually) over the table, especially if helps with the ORDER BY.

An optimization that it could do (but does not seem to do) for your original query is...
"You want one row, and you don't care which one? Let me look in the buffer_pool. Aha, here is a block of that table. Let me give you some row from that block."
That would prevent an unnecessary disk read, and would make the result somewhat 'random'.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Does LIMIT 1 without ORDER BY ID bring the record with minimum ID?
2506
May 03, 2012 09:24PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.