MySQL Lists are EOL. Please join:

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

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