Advanced MySQL 8

LIMIT 1 when getting a unique row

LIMIT is another feature that restricts the result set's size based on the number of rows provided. When getting a unique row, it depends on what kind of column the where clause is filtering. For example, if the where clause is filtering on a constrained column (primary key/index), then it wouldn't have any improved performance, as it already knows the location of the filtered row, whereas, on the other hand, if it is used on a non-constrained column, then the overhead of the full table can be eliminated, as it would stop once that where clause is hit, thus resulting in better efficiency and reducing latency.

The following commands explain different select statements with and without LIMIT:

EXPLAIN SELECT ...