Geek asks and answers

Will LIMIT 0.1 speed up selection on the primary key?

Does anyone know if there is a difference in speed (obviously, for sufficiently significant tables) between these two queries:

SELECT field FROM table WHERE primary_key = "a particular value"

or

SELECT field FROM table WHERE primary_key = "a particular value" LIMIT 0,1

I should note that the field primary_keyis actually the primary key.

Now it LIMIT 0,1helps when the request otherwise will look for other matches. I assume that when the primary key is enabled, it should automatically stop due to its uniqueness. Therefore, I assume that there will be no gain by adding it.

Has anyone come across this before? At the moment, I do not have a sufficiently large data set. I also assume that the same answer will apply for any field that has been set as UNIQUE.

+5
source share
2 answers

So, after reading the information provided by Hammerite (thanks for that), running explainin both queries, you will get:

  • id = 1
  • select_type = SIMPLE
  • table = table
  • type = CONST
  • possible_keys = PRIMARY
  • key = PRIMARY
  • key_len = 767
  • ref = const
  • rows = 1
  • Optional =

Defines both requests as CONST, which the link defines as:

Const

The table contains no more than one corresponding row, which is read at the beginning of the query. Since there is only one row, the values ​​from the column to the rest of the optimizer can be considered a constant. Constant tables are very fast because they are read only once.

Therefore, assuming that I correctly understand both requests, they would be treated the same way - this is what I expected.

+6

, .

.

- Joins, Group_by , sql , , row_count, : 1

, sql , 1 , , :

SELECT Primary_keycoll,field2 FROM table WHERE Primary_keycoll = "value1" or field2 = 'value1' order by field2 limit 0,1 , Primary_keycoll , , : field2 1, mysql , , , ,

, SQL-. , , .

http://dev.mysql.com/doc/refman/5.0/en/limit-optimization.html

+4

More articles:


All Articles