MySQL optimize queries

We encourage users to post events happening in the community to the community events group on https://www.drupal.org.
heshanlk's picture
Posted by heshanlk on July 7, 2009 at 5:25pm

I'm using Views module to display my contents (Images + Video), I have 50 thumbnails on one page and pagination, I tried Devel and Explain on mysql to get whats going on there
here are the results:
+----+-------------+-----------+--------+-----------------------------------+------------------+---------+---------------------------+-------+--------------------------------------+
| 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 filesort |
| 1 | SIMPLE | term_node | eq_ref | PRIMARY,nid,tid | PRIMARY | 8 | const,Sfv4Drupal.node.nid | 1 | Using where; Using index; Not exists |
+----+-------------+-----------+--------+-----------------------------------+------------------+---------+---------------------------+-------+--------------------------------------+

Seems filesort is the performance bottleneck so what can I do with this? to ooptimize query with Views, or do I need to remove Views?

Thanks

Comments

More information

Posted by ronaldbradford on July 7, 2009 at 7:58pm
ronaldbradford's picture

Can you post the full query you are executing, as well as

SHOW CREATE TABLE node\G
SHOW CREATE TABLE term_node\G
SHOW GLOBAL VARIABLES LIKE 'sort_buffer_size';
SHOW GLOBAL VARIABLES LIKE 'tmp_table_size';
SHOW GLOBAL VARIABLES LIKE 'max_heap_table_size';
SELECT VERSION();

The filesort can be optimized various ways depending on the query being executed.
You are also trying to sort 46,000 rows, so it's bound to take a while. The query will help identify if you can restrict this more.

This may be a stupid

Posted by SerenityNow on July 8, 2009 at 1:42am
SerenityNow's picture

This may be a stupid question, but...

If only 50 images are being displayed, is it really necessary to sort 46,000 rows to get those images? It seems like there is probably a more optimal query strategy. (maybe create an index using the sort field?)

here is a one query that I'm

Posted by heshanlk on July 8, 2009 at 5:18am
heshanlk's picture

here is a one query that I'm running, this was generated by views, and I used Devel to find this query. I have about 6 pages build with views (same query only change display order any display type, like by date etc ), those MySQL queries are generation heavy load on my MySQL server.

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


For Pagine
----------
SELECT count(node.nid) 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)

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 GLOBAL VARIABLES LIKE 'sort_buffer_size';
+------------------+---------+
| Variable_name    | Value   |
+------------------+---------+
| sort_buffer_size | 6291448 |
+------------------+---------+
1 row in set (0.00 sec)


mysql> SHOW GLOBAL VARIABLES LIKE 'tmp_table_size';
+----------------+-----------+
| Variable_name  | Value     |
+----------------+-----------+
| tmp_table_size | 268435456 |
+----------------+-----------+
1 row in set (0.00 sec)


mysql> SHOW GLOBAL VARIABLES LIKE 'max_heap_table_size';
+---------------------+----------+
| Variable_name       | Value    |
+---------------------+----------+
| max_heap_table_size | 16777216 |
+---------------------+----------+
1 row in set (0.00 sec)


mysql> SELECT VERSION();
+-----------+
| VERSION() |
+-----------+
| 5.0.27    |
+-----------+
1 row in set (0.00 sec)

Senior Drupal Developer at DrupalConnect

mysql> show create table

Posted by heshanlk on July 8, 2009 at 6:04am
heshanlk's picture

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)

Senior Drupal Developer at DrupalConnect

If you select from one

Posted by catch on July 8, 2009 at 1:15pm
catch's picture

If you select from one table, and do conditions, sort and/or order by on another table, you're always going to get bad queries - doesn't matter if you use Views or not. If you're very, very good at writing MySQL queries and fully understand how indexes work, you might be able to do better, but otherwise you should look at simplifying the view (don't sort by votes etc.).

If you really, really need those views, I'd suggest using block caching or the new Views 2.5/6 views caching on them - that will massively reduce overall load (even if the queries themselves remain slow - but they should run every few minutes instead of every request).