Stop MySQL after first match

Question

I noticed that adding LIMIT 1 at the end of a query does not decrease the execution time. I have a few thousand records and a simple query. How do I make MySQL stop after the first match?

For example, these two queries both take approximately half a second:

SELECT id,content FROM links WHERE LENGTH(content)<500 ORDER BY likes

SELECT id,content FROM links WHERE LENGTH(content)<500 ORDER BY likes LIMIT 1

Edit: And here's the explain results:

id | select_type | table | type possible_keys | key | key_len | ref | rows | Extra
1 | SIMPLE | links | ALL | NULL | NULL | NULL | NULL | 38556 | Using where; Using filesort

Answer 1

The difference between the two queries run time relies on the actual data.

There are several possible scenarios:

There are many records with LENGTH(content)<500

In this case, MySQL will start scanning all table rows (according to primary key order since you didn't provide any ORDER BY). There is no index use since your WHERE condition can't be indexed. Since there are relatively many rows with LENGTH(content)<500, the LIMIT query will return faster than the other one.

There are no records with LENGTH(content)<500 Again, MySQL will start scanning all table rows, but will have to go through all records to figure out none of them satisfies the condition. Again no index can be used for the same reason. In this case - the two queries will have exactly the same run time.

Anything between those two scenarios will have different run times, which will be farther apart as you have more valid records in the table.

Edit Now that you added the ORDER BY, the answer is a bit different:

• If there is an index on likes column, ORDER BY would use it and the time would be the time it takes to get to the first record that satisfies the WHERE condition (if 66% of the records do, than this should be faster than without the LIMIT).

• If there is no index on likes column, the ORDER BY will take most of the time - MySQL must scan all table to get all records that satisfy the WHERE, then order them by likes, and then take the first one. In this case both queries will have similar run time (scanning and sorting the results is much longer than returning 1 record or many records...)!