I am very new to sql and query optimization, I am sorry if its inappropriate to ask such as may be asked previously. But I need help and some thoughts where I can look and start ...
EXPLAIN
SELECT DISTINCT node.nid AS nid,
node.title AS node_title,
votingapi_cache_node_points_VotingNumber_sum.value AS votingapi_cache_node_points_votingnumber_sum_value,
node.created AS node_created,
votingapi_cache_node_hot_VotingNumber_reddit.value AS votingapi_cache_node_hot_votingnumber_reddit_value,
'node' AS field_data_field_url_node_entity_type,
'node' AS field_data_field_images_node_entity_type,
'node' AS field_data_title_field_node_entity_type,
'node' AS field_data_field_topic2_node_entity_type
FROM node node
LEFT JOIN votingapi_cache votingapi_cache_node_hot_VotingNumber_reddit
ON node.nid = votingapi_cache_node_hot_VotingNumber_reddit.entity_id
AND (votingapi_cache_node_hot_VotingNumber_reddit.entity_type = 'node'
AND votingapi_cache_node_hot_VotingNumber_reddit.value_type = 'hot'
AND votingapi_cache_node_hot_VotingNumber_reddit.tag = 'VotingNumber'
AND votingapi_cache_node_hot_VotingNumber_reddit.function = 'reddit')
LEFT JOIN votingapi_cache votingapi_cache_node_points_VotingNumber_sum
ON node.nid = votingapi_cache_node_points_VotingNumber_sum.entity_id
AND (votingapi_cache_node_points_VotingNumber_sum.entity_type = 'node'
AND votingapi_cache_node_points_VotingNumber_sum.value_type = 'points'
AND votingapi_cache_node_points_VotingNumber_sum.tag = 'VotingNumber'
AND votingapi_cache_node_points_VotingNumber_sum.function = 'sum')
INNER JOIN taxonomy_index taxonomy_index
ON node.nid = taxonomy_index.nid
LEFT JOIN field_data_title_field field_data_title_field
ON node.nid = field_data_title_field.entity_id
AND (field_data_title_field.entity_type = 'node'
AND field_data_title_field.deleted = '0')
WHERE (( (node.status = '1')
AND (node.type IN ('link'))
AND (taxonomy_index.tid = '1')
AND (field_data_title_field.language IN ('sv'))
AND (votingapi_cache_node_points_VotingNumber_sum.value >= '1')
))
ORDER BY votingapi_cache_node_hot_votingnumber_reddit_value DESC
LIMIT 10 OFFSET 0
Explain shows like below image ie: using Using temporary; Using filesort etc
Please suggest ! Thanks!
field_data_title_field
andvotingapi_cache_node_points_VotingNumber_sum
tables inWHERE
section theLEFT JOIN
of that tables degenerates toINNER JOIN
. Replace them.LEFT JOIN
for two tables mentioned above. So you wiil have 3 inner joins and 1 left join.The only left-joined table wiil bevotingapi_cache_node_hot_VotingNumber_reddit
.Using temporary; Using filesort
is a result of output sorting, I do not see the way to remove this operations.