Re: Order by using filesort on where

[Rick James]

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,
        callback_url
    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:
[/code]
ALTER TABLE out_p
DROP INDEX index_status,
ADD INDEX (status, r_id, msg_id);

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