Hi!
I find the following query in my slow query log:
SELECT n.* FROM node n LEFT JOIN xmlsitemap_node xn ON xn.nid = n.nid WHERE xn.nid IS NULL AND n.status <> 0 LIMIT 0, 100
I'm not an SQL expert, so I don't know what is the problem with this query, but I attached the explain of this query. I think the main problem with this query, it doesn't use key for select in node table. This query was logged for every database which use the xmlsitemap module, no matter how many nodes in the node table.
| Comment | File | Size | Author |
|---|---|---|---|
| #2 | xmlsitemap_exlpain_modified_query_20090905_1.pdf | 27.15 KB | xmarket |
| xmlsitemap_slow_query.pdf | 27.14 KB | xmarket |
Comments
Comment #1
dave reidCould you try the same query with
WHERE ... n.status = 1instead ofn.status <> 0?Comment #2
xmarket commentedHi!
Here is the explain of the modified query.
Comment #3
Anonymous (not verified) commentedWhy isn't the engine using the node_status_type index it identified? It is doing a full table scan why? What version of MySQL are you using?
Comment #4
xmarket commentedMySQL 5.0.83 with Percona patchset 5.0.83-b17. It's a gentoo package.
I really don't know the reason why. :S
I've got the same result with the latest gentoo package MySQL 5.0.84 with Percona patchset 5.0.84-b18 and the custom built MySQL 5.1.38 without Percona patchset.
Comment #5
xmarket commentedHi!
I gave a chance to the latest MySQL release. Unfortunately the explain result remained the same with MySQL 5.0.86 too.
Comment #6
Anonymous (not verified) commented@xmarket: Can you run this through the MySQL support venues? We really need to know why the index is being ignored and how to change the query.
Comment #7
dave reidI don't think this query is ignore the index on the {node} table. In fact the query log shows that it's only using a WHERE and not a FILESORT, so that's the best it can get there. The part about "Using index; Not exists" is just fine because it's using the proper index on {xmlsitemap_node} and the query is a not exists type of query. I really don't see how this could be optimized further.
Similar query in 6.x-2.x returns the same type of result:
SELECT n.nid FROM node n LEFT JOIN xmlsitemap x ON x.type = 'node' AND n.nid = x.id WHERE x.id IS NULL ORDER BY n.changed DESCThis should be postponed unless a MySQL and/or PostgreSQL guru can review this.
Comment #8
dave reidI can't see what else we can do here to help, so marking as by design.