Today at work, I tried executing the following command in Teradata and failed:
SELECT TOP 10 * FROM tab WHERE col <> 1
As a coworker explained to me later, Teradata first runs the WHERE condition on the entire table (way too big), before selecting TOP 10. According to my coworker, things would be different if col was indexed.
Now I wonder if MySQL is actually doing the same; potentially I just never pushed the boundaries enough. Does
SELECT * FROM tab WHERE col != 1 LIMIT 10
also run the col != 1 condition on the entire table before returning the first 10 (let col not be indexed if this changes the answer)?
Thanks a bunch in advance.
First, you should not use LIMIT / TOP unless you also use ORDER BY, if you are interested in what exactly is returned (other than the fact there are so many records at all).
Having said that, MySQL will not scan the whole table (if tab is a table) or index prior to returning the LIMIT records: it will return the records as it scans and filters them, and will stop as soon as there are enough of them.
However, if tab is not a table, but a view, and if it contains a nested query, or a GROUP BY condition or something like that, MySQL might need to scan all the underlying tables prior to filtering and limiting indeed.