At 23:20 -0400 7/2/02, Tac wrote: >I have a table with a "state" field, and often I want to get only records >matching those states, ordered. This simple query > > Select * from my_table where state='NJ' order by table_id DESC > >is relatively slow because of the order by clause (where there are a lot of >matching records). I've tried everything I can think of to speed this up -- >indexing state and ID together, extracting the records (just the table_id's) >matching the state into a temporary table and then inner joining it back >with the original table, etc. > >EXPLAIN shows that it has to scan through the entire result set to order it, >but in the case where there are lots of matching records (and the records >themselves are large, with text blobs), it's very slow. It's fast without >the order by. > >Basically, I want a super-fast way to say "Get me the most recently stored x >records matching this criteria". Any suggestions on speeding this up? >(Every day I look on mysql.com to see if 4.02 is out, because this issue >will largely go away when query caching is available, and I don't want to >implement that logic in my code now.)
You don't have to wait for 4.0.2. This particular issue is fixed in 4.0.0 (ORDER BY ... DESC can use indexes). > >TIA, > >Tac --------------------------------------------------------------------- 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 <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
