Re[2]: Re: SQL WHERE datetime > NOW

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Hello John,

Tuesday, January 6, 2004, 6:00:09 PM, you wrote:

J> http://www.mysql.com/doc/en/LIMIT_optimisation.html
J> If you use LIMIT row_count with ORDER BY, MySQL will end the sorting as soon
J> as it has found the first row_count lines instead of sorting the whole
J> table.

I wish it did. Rarely in practise do I ever see this happen. I wonder
if it's version specific?

Real world example from a 191,404 row table:

explain select messageid from message order by messageid limit 0,50

>From the profile:

table,type,possible_keys,key,key_len,ref,rows,Extra
message,index,NULL,PRIMARY,4,NULL,191404,Using index

So it's definitely doing something with 191,404 rows. It's sorting the
entire table based on the index and bringing back the 50 rows I asked
for. Which does make sense I guess (even if it does seem to contradict
the MySQL manual)

J> I meant to largest 10 values in a column.  For example, I have a record with
J> ID number 1800 in an auto increment field, table has 1805 records.  When I
J> view the table 'raw' record 1800 appears after record 12.  I wanted records
J> 1755-1805.  Instead now I've retrieved the whole table with ORDER BY without
J> LIMIT and put my start and end values in the for () loop.  Now, my eyes may
J> have deceived me...

Use ORDER BY and LIMIT together? Unless you are doing this already,
not sure.

Assuming your auto increment field is called "id":

SELECT * FROM table ORDER BY id DESC LIMIT 0,50

-- 
Best regards,
 Richard                            mailto:rich@xxxxxxxxxxxxxxxx


[Index of Archives]     [PHP Home]     [PHP Users]     [Postgresql Discussion]     [Kernel Newbies]     [Postgresql]     [Yosemite News]

  Powered by Linux