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.)
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
