List:General Discussion« Previous MessageNext Message »
From:Gordon Date:October 27 2002 4:02am
Subject:RE: Problem with select.
View as plain text  
This is what happens when you use LIMIT

MySQL retrieves the entire record set for the select and then sends the
number requested in the LIMIT parameter to the client. MySQL has to
retrieve the entire result set to accommodate a LIMIT clause which skips
n records i.e. LIMIT 250,10.

With no ORDER BY or WHERE clause all records in the table are retrieved
in the order they were originally loaded {at least true for MyISAM table
type}. If the primary key is an autoincrement field then this will be in
primary key sequence, but this is coincedental. If the primary key is
not auto increment and the records are not loaded in primary key
sequence then the result set will not be in primary key order.

So if you want to use an index use WHERE and if you want a specific
sequence use ORDER BY. 



> -----Original Message-----
> From: Andrey Hristov [mailto:andrey@stripped] 
> Sent: Wednesday, October 23, 2002 9:47 AM
> To: mysql@stripped
> Subject: Problem with select.
> 
> 
> HI,
> I don't know is it bad or not but I face queries the work 
> quite long time.
> 
> the sql is :
>  select * from log_answers_index limit 0,10
> 
> and it takes too much time for me to receive the result.
> I got indexes on most of the fields, i got primary key which 
> is auto_increment int(11). When I did explain on the query I 
> got this : type : ALL rows : 123010 all other cells are empty.
> 
> When I do :
> select * from log_answers_index where log_entry_index between 
> 1 and 10 i got type : range
> 
> For me it is strange that the primary key is not used when 
> there is LIMIT clause without WHERE one.
> 
> Best regards
> Andrey Hristov
> 
> 
> 
> 
> ---------------------------------------------------------------------
> Before posting, please check:
>    http://www.mysql.com/manual.php   (the manual)
>    http://lists.mysql.com/           (the list archive)
> 
> To request this thread, e-mail <mysql-thread123045@stripped>
> To unsubscribe, e-mail 
> <mysql-unsubscribe-gordon=interstatesoftware.com@stripped>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
> 

Thread
Problem with select.Andrey Hristov23 Oct
  • Re: Problem with select.gerald_clark23 Oct
  • Re: Problem with select.Andrey Hristov23 Oct
    • Re: Problem with select.Sinisa Milivojevic24 Oct
  • Re: Problem with select.gerald_clark23 Oct
  • RE: Problem with select.Gordon27 Oct