Analyzing slow mysql querries Our Drupal site has recently started to get much more traffic and often pegs the CPU and memory when we have 50-100 active connections. We have 8cpus and 16GB of RAM. I notice that when the cpu is pegged mysql is by far the largest consumer so I decided to look at the mysql-slow.log and found that some queries take over 1 second and I am wondering if this is reasonable. Here is one example:

# Time: 150330 12:01:34
# User@Host: root[root] @ localhost []
# Query_time: 1.131631  Lock_time: 0.000301 Rows_sent: 4  Rows_examined: 151102
SET timestamp=1427738494;
SELECT 
  node.nid AS nid, 
  node.title AS node_title, 
  users_node.name AS users_node_name, 
  users_node.uid AS users_node_uid, 
  votingapi_cache_node_points_projects_sum.value AS votingapi_cache_node_points_projects_sum_value, 
  node.created AS node_created, 
  node.changed AS node_changed, 
  node_revision.timestamp AS node_revision_timestamp, 
  'node' AS field_data_field_screenshot_node_entity_type
FROM node AS node
INNER JOIN users AS users_node 
  ON node.uid = users_node.uid
LEFT JOIN votingapi_cache AS votingapi_cache_node_points_projects_sum 
  ON node.nid = votingapi_cache_node_points_projects_sum.entity_id 
  AND votingapi_cache_node_points_projects_sum.entity_type = 'node' 
  AND votingapi_cache_node_points_projects_sum.value_type = 'points' 
  AND votingapi_cache_node_points_projects_sum.tag = 'projects' 
  AND votingapi_cache_node_points_projects_sum.function = 'sum'
LEFT JOIN votingapi_vote AS votingapi_vote_node_points_vote 
  ON node.nid = votingapi_vote_node_points_vote.entity_id 
  AND votingapi_vote_node_points_vote.entity_type = 'node' 
  AND votingapi_vote_node_points_vote.value_type = 'points' 
  AND votingapi_vote_node_points_vote.tag = 'vote'
LEFT JOIN node_revision AS node_revision 
  ON node.vid = node_revision.vid
WHERE node.status = '1'
  AND node.type IN ('arcade_project_container')
  AND EXISTS (
    SELECT na.nid AS nid
    FROM node_access AS na
    WHERE na.gid = '0' 
    AND na.realm = 'all'
    AND na.grant_view &gt;= '1' 
    AND node.nid = na.nid
  )
ORDER BY 
  node_created DESC, 
  node_changed DESC, 
  node_revision_timestamp DESC
LIMIT 4 OFFSET 5800;


This query comes from the front page where I have a view which shows the most recent submissions. Should this query really take 1.13 seconds?

EDIT: Here is a screenshot of the results of running EXPLAIN on this query on phpmyadmin:



Here is the EXPLAIN after switch nid and uid as suggested by mikeytown2:
Add a new index of type index on the node table with these 7 columns; I would label it "status_type_uid_nid_vid_created_changed"

status, type, uid, nid, vid, created, changed


Once you have done that run explain again; odds are the index will need to be re-ordered or another table will need to have an index added to it.

Try #2. Flip nid &amp; uid.

status_type_nid_uid_vid_created_changed

status, type, nid, uid, vid, created, changed