CODEWITHSUNDEEP
mysqlperformancequery-optimization
user2434
user2434

Reputation: 6399

Can we optimize this SQL Query?

I have this Query that takes at least 1 minute to run. It uses a lot of Left Joins. Is there any way to make it faster ? I'm new to SQL, so help would be appreciated.

   SELECT
    distinct cnt.last_name,
    cnt.bus_ph_num,
    cnt.email_addr,
    usr.login,
    cnt.fst_name,
    cnt.fk_id  
from
    contact cnt
LEFT JOIN
    transaction tran 
        ON tran.id=cnt.fk_id    
left join
    party party 
        on party.pk_id=cnt.fk_id  
left join
    user usr 
        on usr.fk_id=cnt.fk_id  
left join
    role role
on      
    usr.role_id = role.pk_row_id
where
    cnt.status_cd=1  
    and   party.fk_id= 'xyz'  
    and (
        usr.login  like '%somevalue%'  
        and  (
            tran.txn_trk_num is  null ||  tran.txn_trk_num like '%somevalue%' 
        ) 
        and  cnt.last_name like '%somevalue%'  
        and  cnt.email_addr like '%somevalue%'  
        and  cnt.busin_ph_num like '%somevalue%'    
    ) 
    and (
        tran.txn_trk_num is  null || tran.txn_typ=1 || tran.txn_typ=2 || tran.txn_typ=3
    ) 
    and (role.role_name ='ROLE_ADMIN')

Upvotes: 0

Views: 60

Answers (1)

Simon at The Access Group
Simon at The Access Group

Reputation: 7027

Right I've tried to do what I can. Mostly some reordering, some swapping to INNER JOINs and movement of WHERE syntax to the joins. Some renaming of aliases because you had used alias names matching table names negating their purpose.

This will return rows where there are no matching transactions, so you might want to change that join to be INNER also, rather depends on the purpose / intended output, but this should be a good starting point. It allows MySQL to reduce the number of rows it looks at.

Further improvement can be had by suitable indexes, but advising on these is hard without knowing data types / the variance in the data etc.

SELECT DISTINCT
    cnt.last_name,
    cnt.bus_ph_num,
    cnt.email_addr,
    u.login,
    cnt.fst_name,
    cnt.fk_id  

FROM contact cnt

-- Changed  to INNER JOIN as the WHERE indicates this is required
INNER JOIN party p 
    ON p.pk_id=cnt.fk_id  
    -- Moved this limiting statement to the join
    AND p.fk_id= 'xyz' 

-- Changed to INNER JOIN as the LIKE % indicates htis is required
INNER JOIN user u 
    ON u.fk_id=cnt.fk_id 
    -- OP added later, quite inefficient due to use of wildcards on both sides causing scans
    AND u.login LIKE '%somevalue%'  

-- Also here, INNER, as the WHERE means this is required
INNER JOIN role r
    ON r.pk_row_id = u.role_id
    -- Moved this limiting statement to the join
    AND r.role_name ='ROLE_ADMIN'

LEFT JOIN transaction tran 
    ON tran.id=cnt.fk_id
    -- Moved this limiting statement to the join
    AND tran.txn_typ IN ( 1 , 2 , 3 )
    -- OP added later, quite inefficient due to use of wildcards on both sides causing scans
    AND tran.txn_trk_num LIKE '%somevalue%' 

WHERE cnt.status_cd = 1  
    -- OP added later, quite inefficient due to use of wildcards on both sides causing scans
    AND cnt.last_name LIKE '%somevalue%'  
    AND cnt.email_addr LIKE '%somevalue%'  
    AND cnt.busin_ph_num LIKE '%somevalue%'

If you can get rid of those LIKE statements it will get better also. The following are able to use an index if a suitable one is available :

  • ... LIKE 'somevalue'
  • ... = 'somevalue'
  • ... LIKE 'somevalue%'

However ... LIKE '%somevalue%' is unable to use an index

Upvotes: 2

Related Questions