MySQL Forums
Forum List  »  Newbie

Re: Order by using filesort on where
Posted by: Rick James
Date: March 18, 2009 08:21PM

Thanks for the details you presented, it made it possible to spot the issue without having to ask for more info.

Having lots of one-column keys may not be optimal. Consider compound keys.

First to make the query more readable:
SELECT  ms_id, r_id, gsm_num, access_code,
        msg, header, bindata, dcs, status,
        operator, tariff, err_code, svc_type,
    from  out_p
    where  status = 0
      and  ms_id > 255109
      and  r_id = 3
    order by  ms_id
    limit  100;

Rows_sent: 100 Rows_examined: 35143 -- this says that it could not really optimize the query.

This will probably make the query so fast it won't show up in the slowlog:
DROP INDEX index_status,
ADD INDEX (status, r_id, msg_id);

Please use [ code ] and [ / code ] around any code, SQL or output.

Options: ReplyQuote

Written By
Re: Order by using filesort on where
March 18, 2009 08:21PM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.