Slow Query
kcoop - August 27, 2009 - 22:49
| Project: | Comment RSS |
| Version: | 6.x-2.x-dev |
| Component: | Code |
| Category: | bug report |
| Priority: | normal |
| Assigned: | Unassigned |
| Status: | needs review |
Description
My site has been experiencing sporadic spikes in mysql connections, and in the course of investigating, we've discovered a slow query in CommentRSS that is taking typically 45 seconds to execute against a comments table of about 750k rows. A consultant I'm working with has rewritten the query (line 168, commentrss.pages.inc), from this:
$SQL = 'SELECT '. $nidselector .', c.cid, c.subject, c.comment, c.timestamp, c.uid, c.name, c.format, u.name username, n.title FROM {node} n '. $joins .' INNER JOIN {comments} c ON c.nid = n.nid INNER JOIN {users} u ON c.uid = u.uid WHERE '. $where .' n.status = 1 AND c.status = %d ORDER BY c.timestamp DESC';to this:
$SQL = 'SELECT STRAIGHT_JOIN '. $nidselector .', c.cid, c.subject, c.comment, c.timestamp, c.uid, c.name, c.format, u.name username, n.title FROM {comments} c '. $joins .' INNER JOIN {node} n ON c.nid = n.nid INNER JOIN {users} u ON c.uid = u.uid WHERE '. $where .' n.status = 1 AND c.status = %d ORDER BY c.timestamp DESC';and then added an index for timestamp and uid. It's reduced the query time to 6 seconds. There may be a better way, but it's improved things on the site considerably for now. Thought I'd report it in case anyone else runs up against such a thing.
Sorry I don't have a patch - still kind of clueless in that area.
#1
Marked #567668: Page load is extremely slow with many comments as a duplicate of this issue. This needs an alternate solution because STRAIGHT_JOIN is a MySQL-specific command. It will fail on PostgreSQL.
#2
Maybe just adding the indexes would be a big help? We can implement hook_schema_alter() (with install, uninstall and update) and add our own indexes to the {comments} table. Could you please review this patch to see if it adds the indexes properly and helps speed the query up?
#3
Chris Clark tried this first, but found mysql didn't use the indexes.
#4
Adding an index on status and timestamp (i.e. a single index on both fields) fixed the problem. I did not test with the original query, but it makes the modified query instantaneous.