List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:July 3 2002 3:57am
Subject:Re: Slow Query: matching criteria AND ordering by primary key
View as plain text  
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

Thread
innodb is disabled, how can i make it yes?Hytham Shehab2 Jul
  • Re: innodb is disabled, how can i make it yes?Paul DuBois2 Jul
  • Re: innodb is disabled, how can i make it yes?Hytham Shehab3 Jul
Re: innodb is disabled, how can i make it yes?Heikki Tuuri3 Jul
Re: innodb is disabled, how can i make it yes?Hytham Shehab3 Jul
Re: innodb is disabled, how can i make it yes?Hytham Shehab3 Jul
  • Slow Query: matching criteria AND ordering by primary keyTac3 Jul
    • Re: Slow Query: matching criteria AND ordering by primary keyPaul DuBois3 Jul
Re: innodb is disabled, how can i make it yes?Heikki Tuuri3 Jul
RE: innodb is disabled, how can i make it yes?Bert VdB3 Jul
Re: innodb is disabled, how can i make it yes?Hytham Shehab3 Jul