CODEWITHSUNDEEP
mysqlquery-optimization
Rami Shehadah
Rami Shehadah

Reputation: 59

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

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 ;

enter image description here

Upvotes: 0

Views: 1639

Answers (2)

Bill Karwin
Bill Karwin

Reputation: 562310

The short answer is that you need indexes at least to optimize the lookups done by the JOIN. The explain shows that both tables you are joining are doing a full table scan, then joining them the hard was, using "block nested loop" which indicates it is not using an index.

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

ALTER TABLE c ADD INDEX (project_id);

This would mean there is still a table-scan to read the p table (estimated 5720 rows), but at least when it needs to find the related rows in c, it only reads the rows it needs, without doing a table-scan of 287K rows for each row of p.

The query you posted in an earlier question had another condition:

where DAYNAME(c.EXTRACT_DATETIME) = 'Friday' ;

I don't know why you haven't included this condition in the new question you posted.

If this is still a condition you need to handle, this could help optimize the query further. MySQL 5.7 (which you said in the other question you are using) supports virtual columns, defined for an expression, and you can index virtual columns.

ALTER TABLE c 
  ADD COLUMN isFriday AS (DAYNAME(EXTRACT_DATETIME) = 'Friday'),
  ADD INDEX (isFriday);

Then if you search on the new isFriday column, or even if you search on the same expression used for the virtual column definition, it will use the index.

So what you really need is an index on c that uses both columns, one for the join, and then for the additional condition.

ALTER TABLE c 
  ADD COLUMN isFriday AS (DAYNAME(EXTRACT_DATETIME) = 'Friday'),
  ADD INDEX (project_id, isFriday);

Upvotes: 2

Andrew Sayer
Andrew Sayer

Reputation: 2336

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.

Upvotes: 0

Related Questions