Posted by neokrish on February 9, 2012 at 2:06pm
Hello
I have a query that is being called by drupal which takes about 18 secs to execute and loads the mysql server with 99%cpu usage.
The query is below:
SELECT DISTINCT c.*
FROM
comment c
INNER JOIN node n ON n.nid = c.nid
INNER JOIN node_access na ON na.nid = n.nid
WHERE (c.status = '1') AND (n.status = '1') AND(( (na.gid = '0') AND (na.realm = 'all') )OR( (na.gid = '1') AND (na.realm = 'forum_access') ))AND (na.grant_view >= '1')
ORDER BY c.created DESC, c.cid DESC
LIMIT 10 OFFSET 0;explain for the above query is below:
+----+-------------+-------+--------+------------------------------------------+---------+---------+-------------------+--------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+------------------------------------------+---------+---------+-------------------+--------+----------------------------------------------+
| 1 | SIMPLE | c | ALL | comment_nid_language,comment_num_new,nid | NULL | NULL | NULL | 182670 | Using where; Using temporary; Using filesort |
| 1 | SIMPLE | na | ref | PRIMARY,nid | nid | 4 | therevival.c.nid | 5 | Using where; Distinct |
| 1 | SIMPLE | n | eq_ref | PRIMARY,node_status_type | PRIMARY | 4 | therevival.na.nid | 1 | Using where; Distinct |
+----+-------------+-------+--------+------------------------------------------+---------+---------+-------------------+--------+----------------------------------------------+Individual table structures are as below:
mysql> describe comment;
+----------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+---------------------+------+-----+---------+----------------+
| cid | int(11) | NO | PRI | NULL | auto_increment |
| pid | int(11) | NO | MUL | 0 | |
| nid | int(11) | NO | MUL | 0 | |
| uid | int(11) | NO | MUL | 0 | |
| subject | varchar(64) | NO | | | |
| hostname | varchar(128) | NO | | | |
| changed | int(11) | NO | | 0 | |
| status | tinyint(3) unsigned | NO | | 1 | |
| thread | varchar(255) | NO | | NULL | |
| name | varchar(60) | YES | | NULL | |
| mail | varchar(64) | YES | | NULL | |
| homepage | varchar(255) | YES | | NULL | |
| language | varchar(12) | NO | | | |
| created | int(11) | NO | MUL | 0 | |
+----------+---------------------+------+-----+---------+----------------+mysql> describe node_access;
+--------------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+---------------------+------+-----+---------+-------+
| nid | int(10) unsigned | NO | PRI | 0 | |
| gid | int(10) unsigned | NO | PRI | 0 | |
| realm | varchar(255) | NO | PRI | | |
| grant_view | tinyint(3) unsigned | NO | | 0 | |
| grant_update | tinyint(3) unsigned | NO | | 0 | |
| grant_delete | tinyint(3) unsigned | NO | | 0 | |
+--------------+---------------------+------+-----+---------+-------+mysql> describe node;
+-----------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+------------------+------+-----+---------+----------------+
| nid | int(10) unsigned | NO | PRI | NULL | auto_increment |
| vid | int(10) unsigned | NO | UNI | 0 | |
| type | varchar(32) | NO | MUL | | |
| language | varchar(12) | NO | | | |
| title | varchar(255) | NO | MUL | | |
| uid | int(11) | NO | MUL | 0 | |
| status | int(11) | NO | MUL | 1 | |
| created | int(11) | NO | MUL | 0 | |
| changed | int(11) | NO | MUL | 0 | |
| comment | int(11) | NO | | 0 | |
| promote | int(11) | NO | MUL | 0 | |
| sticky | int(11) | NO | | 0 | |
| tnid | int(10) unsigned | NO | MUL | 0 | |
| translate | int(11) | NO | MUL | 0 | |
+-----------+------------------+------+-----+---------+----------------+can anyone help me in understanding the query and optimizing it?
thankfully,
neokrish
Comments
Looking at your query you
Looking at your query you have c.status and na.grant_view in the where-clause. Comparing those in the table-definition, it shows that they do NOT have an index or key. So the database basically has to do a table scan on those fields to compare the values. And looking at the explain it runs over 182670 rows. I had the same problem with a CCK content type I have created.
This is not your fault as Drupal does not add indices on all the fields when you install it.
Either add an index manually using the MySQL admin frontend of your choice or write some drupal code:
$ret = array();
db_add_index($ret, 'comment', 'status', array('status'));
db_add_index($ret, 'node_access', 'grant_view', array('grant_view'));
I have not tested the above code, but it should give you an idea.