1

All my question is the title above.

Actually I want to know how limit works in mysql? suppose this table:

// colors
+----+-------+
| id | color |
+----+-------+
| 1  | blue  |
| 2  | red   |
| 3  | green |
| 4  | white |
| 5  | grey  |
| 6  | brown |
| 7  | black |
| 8  | pink  |
+----+-------+

As you know id column is unique (it is PK). And this is my query:

SELECT color FROM colors WHERE id = 5;

Now I want to know, would query above be more efficient if I use LIMIT 1 in the end of that?

stack
  • 10,280
  • 19
  • 65
  • 117
  • `id` is indexed and is unique as it is the PK. So using `LIMIT 1` wouldn't have any effect on performance in my opinion. – Giorgos Betsos Jun 06 '16 at 10:31
  • Possible duplicate of [Which rows are returned when using LIMIT with OFFSET in MySQL?](http://stackoverflow.com/questions/10119291/which-rows-are-returned-when-using-limit-with-offset-in-mysql) – Mohammedshafeek C S Jun 06 '16 at 10:33
  • @MohammedShafeek The question you linked is nothing to do with mine ...! That's about *offset*. – stack Jun 06 '16 at 10:37

2 Answers2

1

no, because you are retrieving one row, if you add limit 1 your dbms will count how many rows and decide if it is necessary to limit result or not.

this is an unnecessary work.

danilonet
  • 1,757
  • 16
  • 33
1

MySQL (and all other rdbms) are very efficient locating records based on primary keys. Adding a limit 1 will not have a significant impact in terms of speed.

However, a limit 1 clause will be handy if the above (or similar) query is used a correlated subquery in the select list. Such queries must return a single record and limit 1 will tell MySQL explicitly that the query cannot return more than 1 row.

select ..., (SELECT color FROM colors WHERE id = outer_query_field limit 1)
from ...
Shadow
  • 33,525
  • 10
  • 51
  • 64
  • It is actually possible to compromise a DB's ability to locate a row by its PK, such as messing with SQL Server Clustered Index, etc. – MatBailie Jun 06 '16 at 10:38
  • @MatBailie What do you mean *"locate a row"* ? – stack Jun 06 '16 at 10:40
  • @MatBailie How would messing with an sql server clustered index impact MySQL's ability to locate a row? What exactly do you mean by messing with an index? – Shadow Jun 06 '16 at 10:42
  • @stack - Shadow is referring to a DB's ability to locate a row in a table based on its primary key and the use of an index or underlying structure of how the data is stored. – MatBailie Jun 06 '16 at 10:42
  • @Shadow - I used SQL Server as the example, not MySQL *(in response to your assertion of 'all other rdbms')*. On SQL Server you could construct a table such that the data is not sorted in relation to the primary key, and that the primary key is also not in any index. – MatBailie Jun 06 '16 at 10:44