"Arthur Radulescu" <[EMAIL PROTECTED]> wrote:
>
> 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...
Create composite index on (cat, date). Use EXPLAIN to see if MySQL uses index:
http://dev.mysql.com/doc/mysql/en/EXPLAIN.html
--
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Egor Egorov
/ /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED]
/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net
<___/ www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
