Ok just some thoughts/guesses.
Quite surprised that your condition on the sentby column (i.e. = "196.11.240.100ISOL_001ConnectPRS") is going to match nearly 11,000 rows according to the EXPLAIN.
By the look of that field value I'd have expected it to match far fewer rows. Of course I don't know your data distribution but from what you've said the table is
usually kept under 5,000 rows so this value seems strange to me. I know it's just an estimate, but even so...
Do you ever run ANALYZE TABLE? If not, your index cardinality stats could be way out which can affect query optimisation
Some info on this for innodb here:
http://dev.mysql.com/doc/refman/5.0/en/innodb-restrictions.html
Anyway, you might find that if you change your index:
KEY `index_status_sentby` (`sentby`,`status`),
to be:
KEY `index_status_sentby_id` (`sentby`,`status`, `id`)
then the filesort goes away.
(run analyze table after doing this)
I'm not 100% sure that it will, but it could be worth a try. If the filesort goes away your query will speed up vastly I suspect.
Post an explain after adding this index and running analyze table, and if this hasn't helped there should be some other things to try.
BTW I'm not sure how long the index addition etc will take so be careful if you do this on your live server :)
Toasty
(as an aside, do you need to use innodb here? It looks like it might be the kind of app that MyISAM is perfect for, despite it's limitations. One to consider if it's possible and if we can't get the speed you need through other means)
Edited 1 time(s). Last edit at 09/01/2006 04:51AM by Toa Sty.