Time Difference b/w LIMIT and without it

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
anjanesh
DevNet Resident
Posts: 1679
Joined: Sat Dec 06, 2003 9:52 pm
Location: Mumbai, India

Time Difference b/w LIMIT and without it

Post 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
Last edited by anjanesh on Tue Aug 09, 2005 12:24 pm, edited 2 times in total.
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post 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 ..
Post Reply