Query Optimization

We've run mysqlsla on our slow query log from the past two days, and the xmlsitemap queries are not showing up at the top of the list. That said, the following query does show up nearly every time cron runs:

SELECT x.loc, x.lastmod, x.changefreq, x.changecount, COALESCE(x.priority_override, x.priority) AS priority FROM xmlsitemap x WHERE x.access = 1 AND x.status = 1 ORDER BY x.loc LIMIT 68000, 1000;

The query was taking 2 seconds to run each time. Fortunately it was easily fixed by replacing an index as follows:

mysql> ALTER TABLE xmlsitemap ADD KEY access_status_loc (access, status, loc);
Query OK, 95432 rows affected (4.67 sec) Records: 95432 Duplicates: 0 Warnings: 0

mysql> ALTER TABLE xmlsitemap DROP KEY access_status;
Query OK, 95432 rows affected (3.63 sec) Records: 95432 Duplicates: 0 Warnings: 0

Prior to this change the query required a filesort. With the new index everything happens in RAM and the query happens in milliseconds.