By reikiman on

My site is a bit slow, the users are complaining, the site is on a shared hosting account meaning I don't have a lot of control e.g. to configure MySQL or most of the other hints on the performance improvement page.

I just turned on the devel module and had it print out queries, the following two took over 200ms apiece. The first is 460ms the second is a bit over 300ms. I've read through the queries and it looks like the columns which are being used in comparisons are all primary keys.

FWIW the page this came from is the 'tracker' page as generated by Views

SELECT DISTINCT(node.nid) AS nid, node.vid AS node_vid, node.title AS node_title, users.name AS users_name, users.uid AS users_uid, node_comment_statistics.comment_count AS node_comment_statistics_comment_count, node_comment_statistics.last_comment_timestamp AS node_comment_statistics_last_comment_timestamp, 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, COALESCE(ncs_users.name, node_comment_statistics.last_comment_name) AS node_comment_statistics_last_comment_name, ncs_users.name AS ncs_users_name, node_comment_statistics.last_comment_uid AS node_comment_statistics_last_comment_uid, users.picture AS users_picture FROM node node INNER JOIN users users ON node.uid = users.uid INNER JOIN node_comment_statistics node_comment_statistics ON node.nid = node_comment_statistics.nid LEFT JOIN history history_user ON node.nid = history_user.nid AND history_user.uid = 1 LEFT JOIN users ncs_users ON node_comment_statistics.last_comment_uid = ncs_users.uid AND ncs_users.uid != '0' WHERE node.status <> 0 ORDER BY node_comment_statistics_last_comment_timestamp DESC LIMIT 0, 50

SELECT COUNT(*) FROM (SELECT DISTINCT(node.nid) AS nid FROM node node INNER JOIN users users ON node.uid = users.uid INNER JOIN node_comment_statistics node_comment_statistics ON node.nid = node_comment_statistics.nid LEFT JOIN history history_user ON node.nid = history_user.nid AND history_user.uid = 1 LEFT JOIN users ncs_users ON node_comment_statistics.last_comment_uid = ncs_users.uid AND ncs_users.uid != '0' WHERE node.status <> 0 ) count_alias

Comments

dnewkerk’s picture

dnewkerk commented

Tracker2 module may be a better replacement for that (it is used here on drupal.org)... it's designed with performance as the key goal, and is a drop-in replacement for core Tracker. Also for more performance tips, there's helpful info on 2bits.

reikiman’s picture

reikiman commented

Hm, If this Tracker2 is better than the default Tracker, is it better than a custom views based tracker? I may not have made it clear but my Tracker is not done with tracker.module but with a custom view. Further my site has several trackers for different slices into the site's content. I doubt Tracker2 is customizable the way a View is. And today with Views why are things like Tracker their own module, why aren't they views? (okay, that's partly because views isn't in core and tracker.module couldn't depend on a contributed module)

In any case the performance problem I'm trying to crack isn't just the Tracker page, that's just the example I had in hand.

The articles on 2bits are interesting. Unfortunately not directly related to the issue in my hand.

I was wondering if there's a guideline or tools for analyzing a slow query and suggesting ways to improve the query.

+ David Herron - 7gen.com/, The Long Tail Pipe, davidherron.com/drupal-blogging-hints