SQL Query Optimization (Mysql 5.7)

Question

I have following query to execute in live mysql 5.7, which takes around 7 sec and we want to reduce their time, please support

SELECT 
 COUNT(
 DISTINCT InternalInquiry.inquiry_id
 ) AS "count" 
FROM
 internal_inquiries `InternalInquiry` 
 LEFT JOIN cpml_projects `Project` 
 ON (
 Project.project_id = InternalInquiry.object_id 
 AND Project.agent_id = 159297
 ) 
 INNER JOIN clients `Client` 
 ON (
 Client.clientid = InternalInquiry.clientID
 ) 
 INNER JOIN 
 (SELECT DISTINCT 
 (InternalInquiry.inquiry_id) AS "id" 
 FROM
 internal_inquiries `InternalInquiry` 
 LEFT JOIN internal_inquiries_sharing `InternalInquirySharing` 
 ON (
 InternalInquiry.inquiry_id = InternalInquirySharing.inquiry_id 
 )
 WHERE (
 (
 InternalInquiry.userid IN ( 399724, 420710, 438389, 535916, 535917, 541643, 541644)
 OR InternalInquirySharing.share_with IN ( 399724, 420710, 438389, 535916, 535917, 541643, 541644)
 ) 
 ) 
 AND (
 InternalInquiry.status IN (
 1, 21, 26, 14, 4, 15, 13, 5, 16, 17, 18, 22, 25, 2, 10, 3, 19, 20, 23, 11, 12, 24, 9, 8, 28
 )
 ) 
 AND (
 InternalInquiry.time_added >= '2013-11-01 00:00:00' 
 AND InternalInquiry.time_added <= '2018-11-22 23:59:59'
 )) dd 
 ON dd.id = InternalInquiry.inquiry_id 
WHERE (
 InternalInquiry.firmstate != 'deleted'
 ) 
 AND (
 InternalInquiry.status IN (
 1, 21, 26, 14, 4, 15, 13, 5, 16, 17, 18, 22, 25, 2, 10, 3, 19, 20, 23, 11, 12, 24, 9, 8, 28
 )
 ) 
 AND (
 InternalInquiry.time_added >= '2013-11-01 00:00:00' 
 AND InternalInquiry.time_added <= '2018-11-22 23:59:59'
 ) 
 AND (
 Client.client_status != 1 AND
 Client.client_agency = 159297
 )

Execution plan as follow:

 id select_type table partitions type possible_keys key key_len ref rows filtered Extra 
------ ----------- ---------------------- ---------- ------ -------------------------------------------------------------------------------------------------------------------- -------------------------------- ------- -------------------------------------- ------ -------- ------------------------------
 1 PRIMARY <derived2> (NULL) ALL (NULL) (NULL) (NULL) (NULL) 143661 100.00 (NULL) 
 1 PRIMARY InternalInquiry (NULL) eq_ref PRIMARY,clientID,time_added,firmstate,search_inquiry_basic_index PRIMARY 4 dd.id 1 12.50 Using where 
 1 PRIMARY Client (NULL) eq_ref PRIMARY,client_agency,client_status,com_1 PRIMARY 4 InternalInquiry.clientID 1 25.00 Using where 
 1 PRIMARY Project (NULL) ref project_id project_id 5 InternalInquiry.object_id 1 100.00 Using where 
 2 DERIVED InternalInquiry (NULL) ALL PRIMARY,clientID,userid,object_type,next_status_wanted,time_added,firmstate,search_inquiry_basic_index,idx_object_id (NULL) (NULL) (NULL) 544996 25.00 Using where; Using temporary 
 2 DERIVED InternalInquirySharing (NULL) ref internal_inquiries_sharing_UN,inquiry_id internal_inquiries_sharing_UN 5 InternalInquiry.inquiry_id 1 100.00 Using where; Using index 

• Rick James: dup of stackoverflow.com/questions/53481249/…

Answer 1

Don't use LEFT unless you are going to use the 'optional' nature it provides. The EXPLAIN seems to imply that the first LEFT made no sense in your case. (I can't tell about the other LEFT.)

These indexes might help:

InternalInquiry: INDEX(status, time_added) -- in this order
Project: INDEX(project_id, agend_id) -- in either order

OR inhibits optimizations; consider trying to avoid it.

To further analyze the query, please figure out how long the derived table (subquery) is taking. It looks like it can be run independently.