3

I'm trying to find out if using "limit 1" would work faster while executing a "select" query on MySQL database with milions of records, like:

SELECT * FROM users where id = 99999;

SELECT * FROM users where id = 99999 LIMIT 1; /* Is it faster? */

I made some tests executing some queries with and without the LIMIT, but the difference between them are really small (using LIMIt has some better speed tho), but I'm not sure if there is any difference between using the "WHERE" in a not indexed column and in an indexed column, if it would have some performance problem or not.

Should I use the "LIMIT 1" in all queries that I want to return only one result even knowing that the "WHERE id = 99999" will return only one row?

Imac
  • 379
  • 5
  • 11
  • So looks like if I'm using the where in an indexed column it won't matter, but it's a "normal" column using LIMIT 1 would be faster? – Imac Apr 03 '17 at 15:49
  • seem it will be faster in any case, but you will "feel" it only on big tables where `id` is not unique – Alex Apr 03 '17 at 16:14

1 Answers1

1

In addition to the question of the performance, I would always use LIMIT in this case. Reason: the quality and readability of the source code increases, since reading the code already determines that only one return value is expected. If it is a column that is not unique, the LIMIT statement is, in my opinion, mandatory to avoid errors by returning multiple values.

Datendenker
  • 264
  • 2
  • 10
  • And I would add that if I had to make a DB engine on my own I would certainly stop iterating through records in case of a LIMIT 1 clause. So I imagine all DB engines stop searching after the first hit and a LIMIT 1 thus increasing performance. – Viktor Joras Jul 09 '19 at 15:01