Views performance issue I have a website with 4 views (and sometimes 5 views) on the homepage. The problem is that when I try to open the website, it takes minutes to load and the cpu goes crazy because of mysql processes.

Screenshot of cpu usage


Output of SHOW PROCESS LIST


I work on a dedicated server with 8GB of RAM, 1900Mhz Intel Hexa-Core Xeon and 4 × 300GB SAS (RAID 10)

I have been using drupal for years, and never encountered such an issue. Any ideas how to fix this?

Update Views query:

SELECT DISTINCT node.title AS node_title, node.nid AS nid, node.language AS node_language, node.created AS node_created, users_node.picture AS users_node_picture, users_node.uid AS users_node_uid, users_node.name AS users_node_name, users_node.mail AS users_node_mail, taxonomy_term_data_node.tid AS taxonomy_term_data_node_tid, taxonomy_term_data_taxonomy_term_hierarchy.tid AS taxonomy_term_data_taxonomy_term_hierarchy_tid, 'node' AS field_data_field_news_photo_node_entity_type, 'node' AS field_data_field_op_section_term_node_entity_type, 'node' AS field_data_body_node_entity_type, 'user' AS field_data_field_user_realname_user_entity_type, 'user' AS field_data_field_user_organization_user_entity_type, 'user' AS field_data_field_profile_picture_user_entity_type
FROM 
{node} node
LEFT JOIN {users} users_node ON node.uid = users_node.uid
LEFT JOIN (SELECT td.*, tn.nid AS nid
FROM 
{taxonomy_term_data} td
LEFT JOIN {taxonomy_vocabulary} tv ON td.vid = tv.vid
LEFT JOIN {taxonomy_index} tn ON tn.tid = td.tid
WHERE  (tv.machine_name IN  ('sections')) ) taxonomy_term_data_node ON node.nid = taxonomy_term_data_node.nid
LEFT JOIN {taxonomy_term_hierarchy} taxonomy_term_data_node__taxonomy_term_hierarchy ON taxonomy_term_data_node.tid = taxonomy_term_data_node__taxonomy_term_hierarchy.tid
LEFT JOIN {taxonomy_term_data} taxonomy_term_data_taxonomy_term_hierarchy ON taxonomy_term_data_node__taxonomy_term_hierarchy.parent = taxonomy_term_data_taxonomy_term_hierarchy.tid
LEFT JOIN {field_data_field_news_photo} field_data_field_news_photo ON node.nid = field_data_field_news_photo.entity_id AND (field_data_field_news_photo.entity_type = 'node' AND field_data_field_news_photo.deleted = '0')
WHERE (( (node.status = '1') AND (node.type IN  ('news')) AND (field_data_field_news_photo.field_news_photo_fid IS NOT NULL ) AND (node.language IN  ('***CURRENT_LANGUAGE***')) ))
ORDER BY node_created DESC
LIMIT 12 OFFSET 0

EXPLAIN output.
The process list is saying that Mysql is writing to tmp tables.  The issue is the way you configured mysql.  Check the my.cnf configuration, and make sure that the temporary tables are large enough.