Re: Does LIMIT 1 without ORDER BY ID bring the record with minimum ID?
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'.