Optimize MySQL queries
I'm using Drupal for Website, its customize and I'm using Views module with it, Views will help to display contents by sorting and grouping.
I have few queries need to optimize, kindly help me to tune these mysql queries.
mysql> SELECT VERSION();
+-----------+
| VERSION() |
+-----------+
| 5.0.27 |
+-----------+
1 row in set (0.00 sec)
Query 1:
mysql> explain SELECT node.nid, votingapi_cache_vote_percent_average.value AS votingapi_cache_vote_percent_average_value FROM node node LEFT JOIN term_node term_node ON node.nid = term_node.nid AND term_node.tid IN ('400566') LEFT JOIN votingapi_cache votingapi_cache_vote_percent_average ON node.nid = votingapi_cache_vote_percent_average.content_id AND votingapi_cache_vote_percent_average.content_type = 'node' AND votingapi_cache_vote_percent_average.value_type = 'percent' AND votingapi_cache_vote_percent_average.tag = 'vote' AND votingapi_cache_vote_percent_average.function = 'average' WHERE (node.type IN ('image','video','acidfree')) AND (node.status = '1') AND (term_node.tid IS NULL) ORDER BY votingapi_cache_vote_percent_average_value DESC;de ON node.nid = term_node.nid AND term_node.tid IN ('400566') LEFT JOIN voti +----+-------------+--------------------------------------+--------+-----------------------------------+------------------+---------+---------------------------+-------+-----------------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------------------------------------+--------+-----------------------------------+------------------+---------+---------------------------+-------+-----------------------------------------------------------+
| 1 | SIMPLE | node | ref | node_type,status,node_status_type | node_status_type | 4 | const | 46002 | Using where; Using index; Using temporary; Using filesort |
| 1 | SIMPLE | term_node | eq_ref | PRIMARY,nid,tid | PRIMARY | 8 | const,Sfv4Drupal.node.nid | 1 | Using where; Using index; Not exists |
| 1 | SIMPLE | votingapi_cache_vote_percent_average | ref | content | content | 68 | const,Sfv4Drupal.node.nid | 2 | |
+----+-------------+--------------------------------------+--------+-----------------------------------+------------------+---------+---------------------------+-------+-----------------------------------------------------------+
3 rows in set (0.00 sec)
mysql> SHOW CREATE TABLE node\G
*************************** 1. row ***************************
Table: node
Create Table: CREATE TABLE node (
nid int(10) unsigned NOT NULL auto_increment,
vid int(10) unsigned NOT NULL default '0',
type varchar(32) NOT NULL default '',
title varchar(128) NOT NULL default '',
uid int(11) NOT NULL default '0',
status int(11) NOT NULL default '1',
created int(11) NOT NULL default '0',
changed int(11) NOT NULL default '0',
comment int(11) NOT NULL default '0',
promote int(11) NOT NULL default '0',
moderate int(11) NOT NULL default '0',
sticky int(11) NOT NULL default '0',
PRIMARY KEY (nid,vid),
UNIQUE KEY vid (vid),
KEY node_type (type(4)),
KEY node_title_type (title,type(4)),
KEY status (status),
KEY uid (uid),
KEY node_moderate (moderate),
KEY node_promote_status (promote,status),
KEY node_created (created),
KEY node_changed (changed),
KEY node_status_type (status,type,nid),
KEY nid (nid)
) ENGINE=MyISAM AUTO_INCREMENT=176875 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
mysql> SHOW CREATE TABLE term_node\G
*************************** 1. row ***************************
Table: term_node
Create Table: CREATE TABLE term_node (
nid int(10) unsigned NOT NULL default '0',
tid int(10) unsigned NOT NULL default '0',
PRIMARY KEY (tid,nid),
KEY nid (nid),
KEY tid (tid)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
mysql> show create table votingapi_cache\G
*************************** 1. row ***************************
Table: votingapi_cache
Create Table: CREATE TABLE `votingapi_cache` (
`vote_cache_id` int(10) unsigned NOT NULL,
`content_type` varchar(20) default NULL,
`content_id` int(10) unsigned default NULL,
`value` float default NULL,
`value_type` varchar(20) NOT NULL,
`tag` varchar(128) default 'vote',
`function` varchar(128) default 'count',
`timestamp` int(11) default NULL,
PRIMARY KEY (`vote_cache_id`),
KEY `content` (`content_type`,`content_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
1 row in set (0.00 sec)