MySQL optimization

ajayg's picture

How to modify this CPU intensive query

Over and over again mysql slow quries log this query taking a long time.
SELECT DISTINCT COUNT(DISTINCT(n.nid)) FROM main_node n LEFT JOIN main_comments c ON n.nid = c.nid AND (c.status = 0 OR c.status IS
NULL) INNER JOIN main_node_access na ON na.nid = n.nid WHERE (na.grant_view >= 1 AND ((na.gid = 0 AND na.realm = 'all') OR (na.gid
= 2 AND na.realm = 'workflow_access') OR (na.gid = 3128 AND na.realm = 'workflow_access_owner') OR (na.gid = 0 AND na.realm = 'og_pu
blic') OR (na.gid = 2205 AND na.realm = 'og_subscriber'))) AND ( n.status = 1 AND (n.uid = 3128 OR c.uid = 3128));

Read more
dwightaspinwall's picture

Request for feedback on best practices in overcoming query performance problems in D6 and beyond

It is well-known that many very useful queries that you create (on your own or via the Views module) are costly to execute because MySQL creates a temporary table and does a filesort. A common example is {node}.type='xxx' and {node}.status='yyy' order by {node_comment_statistics}.comment_count desc.

Another common example is sorting posts in reverse chron order of most recent comment timestamp, which the Views module implements as order by GREATEST({node}.changed, {node_comment_statistics}.last_comment_timestamp) DESC

Read more
localguy's picture

Modifying some views and optimizing site | Elki Media

Employment type: 
Part time
Employment type: 

I have a site that is dragging big time. I need to find someone to identify what is causing some of the problems and fix. I'm in Snohomish and wish to use someone local. The developer I hired to build the site is in NJ and time difference got to be too hard. Please contact me with rates and availability if interested and I will provide more information on the project.

Read more
Subscribe with RSS Syndicate content