Mysql – Slow SELECT in Drupal CMS


I have a slow query from a table with 7000 rows.
After a long time trying to optimize the MySQL conf nothing has changed.
The query:

| 38 | root | localhost | nrj | Query | 28 | Sending data | 

SELECT node.nid AS nid, workflow_node_current.stamp AS workflow_node_current_stamp,
    'node' AS field_data_field_rh_username_node_entity_type,
    'node' AS field_data_field_text_no1_node_entity_type,
    'node' AS field_data_field_membership_number_node_entity_type,
    'node' AS field_data_field_text_1_255_n12_node_entity_type
FROM  node node
LEFT JOIN  workflow_node_history workflow_node_current ON 
    (SELECT max(hid)
     FROM workflow_node_history
     WHERE nid = node.nid AND sid != old_sid 
    ) = workflow_node_current.hid
WHERE (((node.status = '1') AND (node.type IN ('membership_request'))
      AND (workflow_node_current.sid = '85')))
ORDER BY workflow_node_current_stamp DESC

Best Answer

Ouch! I have never seen ON ( SELECT ... ) = .... Rewrite that somehow.

Perhaps more importantly, remove LEFT. You are probably forcing the query start with node, even though it would be much faster to start with a subquery.

sid != old_sid

Which column is in which table? Please use short aliases for all references.

Please provide SHOW CREATE TABLE so we can check that you have adequate indexes.

Please provide EXPLAIN SELECT ... so we can see what the optimizer decided to do.