Page 1 of 1

Time Difference b/w LIMIT and without it

Posted: Tue Apr 12, 2005 2:27 am
by anjanesh
I have a table where the Primary Key (or Unique Key) is UserId
Will code 1 be slower than code 2 ?

Code: Select all

SELECT * FROM `tableName` WHERE UserId='5'

Code: Select all

SELECT * FROM `tableName` WHERE UserId='5' LIMIT 0,1
Will code 1 keep searching for more after it gets the first search unlike code 2 which stops after 1st result found ?
Thanks

Posted: Tue Apr 12, 2005 2:57 am
by timvw
my uneducated guess is that query2 will take longer than query1.

i would expect them to be executed both like this:

1-) from # get the tables to start from
2-) where # eliminate rows that don't match the criteria
3-) select # eliminate columns that don't match the criteria

4-) limit # eliminiate rows that are beyond the limits


http://dev.mysql.com/doc/mysql/en/select.html
The HAVING clause is applied nearly last, just before items are sent to the client, with no optimization. (LIMIT is applied after HAVING.)
Posted by [name withheld] on March 29 2003 2:49am [Delete] [Edit]

when selecting a single random row you have to use a query like this: SELECT ... FROM my_table ORDER BY RAND() LIMIT 1.
as explain shows, mysql optimizes this VERY badly (or may be better said, doens't optimize it at all): it uses an temporary table and an extra filesort.
couldn't this be optimized?!
if not, may be add a syntax like SELECT RANDOM_ROW .... FROM my_table ..