Posted by andribas on August 2, 2010 at 11:22am
I have the following issue;
table node contain rough 200k entries;
I would like to use view which select "node.created, node.title" where node is published, belongs to term (not multiple), and content-type = news. Order by created desc.
Here is query: 255.44ms
SELECT node.nid AS nid, node.title AS node_title, node.created AS node_created FROM node node INNER JOIN term_node term_node ON node.vid = term_node.vid WHERE (node.type in ('news')) AND (node.status <> 0) AND (term_node.tid = 6) ORDER BY node_created DESC LIMIT 0, 10I have several blocks (views) with different terms (term_node.tid).
EXPLAIN gives me following:
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE term_node ref PRIMARY,vid,my_tid my_tid 4 const 39027 Using temporary; Using filesort
1 SIMPLE node eq_ref vid,node_status_type,node_type vid 4 uralpress.term_node.vid 1 Using wherei.e. first selects ALL table term_node with tid=6, then JOIN node, and then sort by node.created.
How I can optimize this query, rows = 39027 in this query is VERY BIG!
Here that view:
<?php
$view = new view;
$view->name = 'news_by_topic';
$view->description = 'Рубрика на главной';
$view->tag = '';
$view->view_php = '';
$view->base_table = 'node';
$view->is_cacheable = FALSE;
$view->api_version = 2;
$view->disabled = FALSE; /* Edit this to true to make a default view disabled initially */
$handler = $view->new_display('default', 'По умолчанию', 'default');
$handler->override_option('fields', array(
'created' => array(
'label' => '',
'alter' => array(
'alter_text' => 0,
'text' => '',
'make_link' => 0,
'path' => '',
'link_class' => '',
'alt' => '',
'prefix' => '',
'suffix' => '',
'target' => '',
'help' => '',
'trim' => 0,
'max_length' => '',
'word_boundary' => 1,
'ellipsis' => 1,
'html' => 0,
'strip_tags' => 0,
),
'empty' => '',
'hide_empty' => 0,
'empty_zero' => 0,
'date_format' => 'small',
'custom_date_format' => '',
'exclude' => 0,
'id' => 'created',
'table' => 'node',
'field' => 'created',
'override' => array(
'button' => 'Переопределить',
),
'relationship' => 'none',
),
'title' => array(
'label' => '',
'alter' => array(
'alter_text' => 0,
'text' => '',
'make_link' => 0,
'path' => '',
'link_class' => '',
'alt' => '',
'prefix' => '',
'suffix' => '',
'target' => '',
'help' => '',
'trim' => 0,
'max_length' => '',
'word_boundary' => 1,
'ellipsis' => 1,
'html' => 0,
'strip_tags' => 0,
),
'empty' => '',
'hide_empty' => 0,
'empty_zero' => 0,
'link_to_node' => 1,
'exclude' => 0,
'id' => 'title',
'table' => 'node',
'field' => 'title',
'relationship' => 'none',
),
));
$handler->override_option('sorts', array(
'nid' => array(
'order' => 'DESC',
'id' => 'nid',
'table' => 'node',
'field' => 'nid',
'relationship' => 'none',
),
));
$handler->override_option('arguments', array(
'tid' => array(
'default_action' => 'default',
'style_plugin' => 'default_summary',
'style_options' => array(),
'wildcard' => 'all',
'wildcard_substitution' => 'Все',
'title' => '',
'breadcrumb' => '',
'default_argument_type' => 'fixed',
'default_argument' => '',
'validate_type' => 'none',
'validate_fail' => 'not found',
'break_phrase' => 0,
'add_table' => 0,
'require_value' => 0,
'reduce_duplicates' => 0,
'set_breadcrumb' => 0,
'id' => 'tid',
'table' => 'term_node',
'field' => 'tid',
'validate_user_argument_type' => 'uid',
'validate_user_roles' => array(
'2' => 0,
),
'relationship' => 'none',
'default_options_div_prefix' => '',
'default_argument_fixed' => '1',
'default_argument_user' => 0,
'default_argument_php' => '',
'validate_argument_node_type' => array(
'panel' => 0,
'links' => 0,
'news' => 0,
'page' => 0,
'reviews' => 0,
),
'validate_argument_node_access' => 0,
'validate_argument_nid_type' => 'nid',
'validate_argument_vocabulary' => array(
'1' => 0,
'3' => 0,
'2' => 0,
),
'validate_argument_type' => 'tid',
'validate_argument_transform' => 0,
'validate_user_restrict_roles' => 0,
'validate_argument_php' => '',
),
));
$handler->override_option('filters', array(
'type' => array(
'operator' => 'in',
'value' => array(
'news' => 'news',
),
'group' => '0',
'exposed' => FALSE,
'expose' => array(
'operator' => FALSE,
'label' => '',
),
'id' => 'type',
'table' => 'node',
'field' => 'type',
'relationship' => 'none',
),
'status' => array(
'operator' => '=',
'value' => '1',
'group' => '0',
'exposed' => FALSE,
'expose' => array(
'operator' => FALSE,
'label' => '',
),
'id' => 'status',
'table' => 'node',
'field' => 'status',
'relationship' => 'none',
),
));
$handler->override_option('access', array(
'type' => 'none',
));
$handler->override_option('cache', array(
'type' => 'none',
));
$handler->override_option('items_per_page', 3);
$handler->override_option('row_options', array(
'inline' => array(
'created' => 'created',
'title' => 'title',
),
'separator' => '',
'hide_empty' => 0,
));
$handler = $view->new_display('block', 'Блок', 'block_1');
$handler->override_option('block_description', '');
$handler->override_option('block_caching', -1);
?>
Comments
The reason
The reason to optimize this query - this is the only query that gives ROWS EXAMINED about 40k, the rest of queries is about 10-100 rows - correct use of index.
Using temporary; Using filesort means 40k rows will be writed to file and then sorted - potential I/O problem.
Variables Handler_read_rnd and Handler_read_rnd_next grows very fast! - this means improper use of indexes.
Another case of this query - Archive, or paging by term. When you select show given date and term i suppose it will be ok (ref for created will be used), but if you decide to list by 20-50 on page - every page will require not all, but about N / 10 (how many terms) scans of database.
If I was a developer of such data schema, i would like to add column created to term_node to remove scans and update this column when edit node table.
create index (tid,created) will fix the problem.
When i searched about this problem I have found someone suggest to sort nodes by nid's instead of node.created. In most cases this would be ok.
Is it correct in my case - create index nid + tid on term_node?
Now it uses
term_node 0 PRIMARY 1 tid A NULL NULL NULL BTREEterm_node 0 PRIMARY 2 vid A 163061 NULL NULL BTREE
term_node 1 vid 1 vid A 163061 NULL NULL BTREE
term_node 1 nid 1 nid A 163061 NULL NULL BTREE
P.S. You may consider 255ms is nothing to worry about, but I have about 10 views with different terms on main page, news adds about 5-10 min, so cache helps, but server is dedicated - 2 x Xeon E5620, on test box C2D it's about 2s and this query, sure incorrect - not uses indexes as it should be.