EXPLAIN 
SELECT n.nid, n.title, u.uid, u.name
FROM node n
INNER JOIN node_counter s ON n.nid = s.nid
INNER JOIN users u ON n.uid = u.uid
WHERE s.totalcount !=0
AND n.status =1
ORDER BY s.totalcount DESC
LIMIT 0 , 5

id  select_type  table  type     possible_keys                 key        key_len       ref     rows       Extra
1    SIMPLE       s      ALL      PRIMARY                       NULL       NULL          NULL    645288     Using where; Using temporary; Using filesort
1    SIMPLE       u      ALL      PRIMARY                       NULL       NULL          NULL    3          Using join buffer
1    SIMPLE       n      eq_ref   PRIMARY,node_status_type,uid  PRIMARY    4             s.nid   1          Using where

table s & table u do not get a key thus it takes a very long time to do this operation with a lot of nodes. I was seeing this take over 300 seconds to complete. It can be cut down by creating an index on daycount; totalcount; in node_counter table. Also creating an index of uid and name in users helps as well.

Any other ideas on how to optimize this SQL?

CommentFileSizeAuthor
#1 explain-out.png10.07 KBmikeytown2

Comments

mikeytown2’s picture

mikeytown2 commented
StatusFileSize
new explain-out.png10.07 KB

Another Query that needs an index, at least on my server. taxonomy_get_tree()

EXPLAIN SELECT t.tid, t. * , parent
FROM term_data t
INNER JOIN term_hierarchy h ON t.tid = h.tid
WHERE t.vid =50
ORDER BY weight, name

Add index to term_hierarchy tid tid & problem fixed.

One more where I can't figure out what to index; would a rewrite of this be the best?
node_admin_nodes()

 EXPLAIN SELECT node.nid AS nid, node.title AS node_title, history_user.timestamp AS history_user_timestamp, history_user.nid AS history_user_nid, node.created AS node_created, node.changed AS node_changed, node.type AS node_type, users.name AS users_name, users.uid AS users_uid, node.status AS node_status, node.uid AS node_uid, node_revisions.format AS node_revisions_format
FROM node node
LEFT JOIN history history_user ON node.nid = history_user.nid
AND history_user.uid =3
INNER JOIN users users ON node.uid = users.uid
LEFT JOIN node_revisions node_revisions ON node.vid = node_revisions.vid
ORDER BY node_changed DESC
LIMIT 20 , 20

Copying to tmp table on disk is what's eating up all the time for the query.

id 	select_type 	table 	type 	possible_keys 	key 	key_len 	ref 	rows 	Extra
1 	SIMPLE 	node 	ALL 	uid 	NULL 	NULL 	NULL 	646277 	Using temporary; Using filesort
1 	SIMPLE 	users 	index 	PRIMARY,statistics 	statistics 	186 	NULL 	3 	Using where; Using index
1 	SIMPLE 	history_user 	eq_ref 	PRIMARY,nid 	PRIMARY 	8 	const,openjurist_drupal.node.nid 	1 	 
1 	SIMPLE 	node_revisions 	eq_ref 	PRIMARY 	PRIMARY 	4 	openjurist_drupal.node.vid 	1

Looks like this last one is getting rewritten only by node_db_rewrite_sql.

Screenshot of the above explain. statistics is the name of the index form the original post. Either way this takes forever to do.

Greg Go’s picture

Greg Go commented

Thanks mikeytown2! Views that sort nodes by popularity are a lot faster after adding indexes to daycount and totalcount.

For other admins who stumble on to this, here's the sql:

mysql> create index totalcount on node_counter (totalcount);

mysql> create index daycount on node_counter (daycount);
mikeytown2’s picture

mikeytown2 commented

@Gregory Go
I have a module that does this now
http://drupal.org/project/dbtuner

jcisio’s picture

jcisio commented

It is only useful if we bulk update node_counter.

If not, increase the index size by 4x for a table of which index needs to be rebuilt every second for a high traffic website is not really a good idea ;-) 88% (either total time or total count) of my slow queries are "UPDATE node_counter" with those extra index. I'm removing them and observe the consequence.

mikeytown2’s picture

mikeytown2 commented
Version: 6.14 » 6.x-dev

Having the node stats block take 30 seconds to render due to slow SQL queries isn't good either (500k+ nodes). With modules like http://drupal.org/project/jstats having writes take a little bit longer for faster reads is a good move.

jcisio’s picture

jcisio commented

Yes, let's make jstats bulk update node_counter, then we can safely add more index to it without any worry.

timmillwood’s picture

timmillwood
Primary language English
Location 🏴󠁧󠁢󠁷󠁬󠁳󠁿 Wales, UK
commented
Status: Active » Closed (duplicate)

indexes don't do much good on node_counter as the data is updated so often.
What it needs is a rewrite, queue, or cache.
#1446932: Improve statistics performance by adding a swappable backend