List:General Discussion« Previous MessageNext Message »
From:Arthur Radulescu Date:April 19 2004 8:11pm
Subject:how to speed up a simple query?
View as plain text  
Hello!

I have a simple query on a table of about 1,000,000 records... The table is
optimized and the query is pretty simple at this moment... something like
this

select id,name,desc,cat,date from table where cat='12'

however I need to order the results by date desc... I have indexes on both
the cat and date (of type timestamp) fields however this operation is much
more slowly when I used the order.... So the result is something like this

select id,name,desc,cat,date from table where cat='12'
takes 0.7 seconds

select id,name,desc,cat,date from table where cat='12' order by date desc
takes 2.4 seconds

any ideea what I can do to help speeding up things? I was thinking that I
should recreate the table daily (something like

insert into temp select * from table order by date desc
delete from table
insert into table select * from temp

) and add the last records at the begining so the mysql database can
retreieve the first records the ones added last because this is my purpose
in fact

But I was wondering if you have any other ideas of how I could do this
because I am not to happy with such operations with such large tables...


Thanks,
Arthur

Thread
Datetime Default ValueStormblade18 Apr
  • Re: Datetime Default Value - I want to know too!Adam18 Apr
  • Re: Datetime Default Value - I want to know too!Stormblade18 Apr
  • Re: Datetime Default ValueMichael Stassen18 Apr
  • Re: Datetime Default ValueStormblade18 Apr
    • Re: Datetime Default ValueMichael Stassen18 Apr
    • Re: Datetime Default ValuePaul DuBois19 Apr
  • Re: Datetime Default ValueRhino18 Apr
  • Re: Datetime Default ValueStormblade18 Apr
  • Re: Datetime Default ValueStormblade18 Apr
  • how to speed up a simple query?Arthur Radulescu19 Apr
    • Re: how to speed up a simple query?Egor Egorov20 Apr
  • Re: Datetime Default ValueStormblade19 Apr
    • Re: Datetime Default ValuePaul DuBois20 Apr
  • Re: Datetime Default ValueStormblade20 Apr
    • Re: Datetime Default ValuePaul DuBois20 Apr
  • MySql Client Program QuestionsDirk Bremer \(NISC\)20 Apr
    • Re: MySql Client Program QuestionsPaul DuBois21 Apr
  • Re: how to speed up a simple query? need some help here...Arthur Radulescu20 Apr
  • Re: MySql Client Program QuestionsDirk Bremer \(NISC\)21 Apr
  • problems with SQL_CALC_FOUND_ROWSArthur Radulescu22 Apr
Re: Datetime Default ValuePaul DuBois19 Apr