Re[2]: [PHP-DB] Re: SQL WHERE datetime > NOW
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, Richardmailto:[EMAIL PROTECTED]
Re: [PHP-DB] Re: SQL WHERE datetime > NOW
"Richard Davey" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > Hello John, > > Tuesday, January 6, 2004, 5:20:22 PM, you wrote: > > J> On further reading, it appears LIMIT stops the query running once x number > J> of records are retrieved without sorting the whole table first. > > Where did you read this? http://www.mysql.com/doc/en/LIMIT_optimisation.html If you use LIMIT row_count with ORDER BY, MySQL will end the sorting as soon as it has found the first row_count lines instead of sorting the whole table. > > If you profile your query you'll notice that in say a 10,000 record > table, if you LIMIT 0,50 but order by something generic (say an ID or > date stamp) it'll still order all 10,000 records before returning the > 50 you asked for. > Does it? > (One of the main reasons I rarely use LIMIT) > > J> Is there a function to get the top x number of results, as there is in MS > J> Access? Eg search on an auto-increment and get the last 10 records added? > > LIMIT 50,-1 > > Retrieve from row 50 to last. I meant to largest 10 values in a column. For example, I have a record with ID number 1800 in an auto increment field, table has 1805 records. When I view the table 'raw' record 1800 appears after record 12. I wanted records 1755-1805. Instead now I've retrieved the whole table with ORDER BY without LIMIT and put my start and end values in the for () loop. Now, my eyes may have deceived me... Cheers, John -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Re: SQL WHERE datetime > NOW
Hello John, Tuesday, January 6, 2004, 5:20:22 PM, you wrote: J> On further reading, it appears LIMIT stops the query running once x number J> of records are retrieved without sorting the whole table first. Where did you read this? If you profile your query you'll notice that in say a 10,000 record table, if you LIMIT 0,50 but order by something generic (say an ID or date stamp) it'll still order all 10,000 records before returning the 50 you asked for. (One of the main reasons I rarely use LIMIT) J> Is there a function to get the top x number of results, as there is in MS J> Access? Eg search on an auto-increment and get the last 10 records added? LIMIT 50,-1 Retrieve from row 50 to last. -- Best regards, Richardmailto:[EMAIL PROTECTED] -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: SQL WHERE datetime > NOW
"John Dillon" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > ...WHERE TO_DAYS(NOW())+1>TO_DAYS(MyTable.MyField) ORDER BY MyTable.MyField DESC LIMIT 30"; > > however, the LIMIT seems to apply before the ORDER BY clause. How can I get the LIMIT to apply after > the ORDER BY? On further reading, it appears LIMIT stops the query running once x number of records are retrieved without sorting the whole table first. Is there a function to get the top x number of results, as there is in MS Access? Eg search on an auto-increment and get the last 10 records added? (They don't always appear at the end of the table, I think if I am editing the table they can appear after the currently active record.) Regards, John -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
