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
