I've logged slow mysql queries on D6 and came up with a large list. So I'm wondering where the queries are located and what is the best strategy to fix them? I also appreciate if you could point me to useful resources about query optimization in Drupal.

  • Indexes ! Take one slow query and add indexes for all. Check this out drupal.org/project/dbtuner – GoodSp33d Jun 26 '12 at 9:18
  • kantu, I already use DB Tunner but it has not solved the problem of long queries (+ 30s). So here I mostly look for manual query optimization guide. – alfish Jun 26 '12 at 9:20
  • Can you post one Slow Query ? And have you tried adding indexes for that particular tables ? – GoodSp33d Jun 26 '12 at 9:23
  • SELECT COUNT(*) FROM (SELECT DISTINCT(comments.cid) AS cid, comments.subject AS comments_subject, comments.nid AS comments_nid, comments.timestamp AS comments_timestamp FROM comments comments LEFT JOIN node node_comments ON comments.nid = ode_comments.nid INNER JOIN users node_comments__users ON ode_comments.uid = node_comments__users.uid WHERE (comments.timestamp < 1340703206+86400) AND (node_comments.status = 1) AND (node_comments__users.uid not in ('5622')) AND (node_comments__users.uid not in ('1')) GROUP BY cid ORDER BY comments_timestamp DESC ) count_alias; – alfish Jun 26 '12 at 9:39
  • The above query takes 34s to execute. How can add index to a particular query like the above? Thanks – alfish Jun 26 '12 at 9:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.