How can i speed up the left join in my query using indexes?-CodePudding

I am new to SQL. At the moment I am experiencing some slower MySQL queries. I think I need to improve my indexes but not sure how.

drop temporary table if exists temp ;
CREATE TEMPORARY TABLE temp
(index idx_a (EXTRACT_DATE, project_id, SERVICE_NAME) ) 

select distinct DATE(c.EXTRACT_DATETIME) as EXTRACT_DATE,p.project_id, p.project_name, c.CLUSTER_NAME, c.SERVICE_NAME,
            UPPER(CONCAT(SUBSTRING_INDEX(c.ENV_NAME, '-', 1),'-',c.CLUSTER_NAME)) as CLUSTER_ID
from  p 
left join  c
on p.project_id = c.project_id ;

CodePudding user response：

The short answer is that you need indexes at least to optimize the lookups done by the JOIN. The explain shows "block nested loop" which indicates it is not using an index.

It would help to at least create an index on c.project_id.

Alternative: create an index on p.project_id and then create a virtual column for DAYNAME(c.extract_dayetime) and index it.

Once you post your CREATE TABLE statements, I may be able to show examples.

CodePudding user response：

You aren’t filtering on anything other than the outer join column. This leads me to expect that most of the rows in both tables are going to need reading. In order to do this only once, you may be best off using a hash join rather than a nested loop and index. A hash join will allow both tables to be read completely once rather than the back and forth approach of a nested loop which will likely mean the same pages read each time a row is looked up.

In order to use hash joins, you need to be running and a version of MySQL at least above version 8. It would be recommended to use the latest available stable release.