CODEWITHSUNDEEP
mysqlsqldatabase
Honey Shah
Honey Shah

Reputation: 421

Inner query or multiple queries which would be result in better performance for mysql?

Inner query:

select up.user_id, up.id as utility_pro_id from utility_pro as up
join utility_pro_zip_code as upz ON upz.utility_pro_id = up.id and upz.zip_code_id=1
where up.available_for_survey=1 and up.user_id not in (select bjr.user_id from book_job_request as bjr where 
((1583821800000 between bjr.start_time and bjr.end_time) and (1583825400000 between bjr.start_time and bjr.end_time)))

Divided in two queries:

  1. select up.user_id, up.id as utility_pro_id from utility_pro as up join utility_pro_zip_code as upz ON upz.utility_pro_id = up.id and upz.zip_code_id=1

  2. Select bjr.user_id as userId from book_job_request as bjr where bjr.user_id in :userIds and (:startTime between bjr.start_time and bjr.end_time) and (:endTime between bjr.start_time and bjr.end_time)

Note: As per my understanding, when single query will be executed using inner query it will scan all the data of book_job_request but while using multiple queries rows with specified user ids will be checked.

Any other better option for the same operation other than these two is also appreciated.

Upvotes: 0

Views: 48

Answers (2)

Rick James
Rick James

Reputation: 142298

Another possibility:

SELECT  up.user_id , up.id utility_pro_id
    FROM  utility_pro up
    JOIN  utility_pro_zip_code upz  ON upz.utility_pro_id = up.id
    WHERE  up.available_for_survey = 1
      AND  upz.zip_code_id = 1
      AND  bjr.user_id IS NULL
      AND NOT EXISTS( SELECT 1 FROM book_job_request
                           WHERE user_id = up.user_id
                             AND end_time   >= 1583821800000
                             AND start_time <= 1583825400000 )

Recommended indexes (for my NOT EXISTS and for Strawberry's LEFT JOIN):

book_job_request:  (user_id, start_time, end_time)
upz:  (zip_code_id, utility_pro_id)
up:  (available_for_survey, user_id, id)

The column order given is important. And, no, the single-column indexes you currently have are not as good.

Upvotes: 0

Strawberry
Strawberry

Reputation: 33945

I expect that the query is supposed to be more like this:

SELECT up.user_id
     , up.id utility_pro_id 
  FROM utility_pro up
  JOIN utility_pro_zip_code upz 
    ON upz.utility_pro_id = up.id
  LEFT
  JOIN book_job_request bjr 
    ON bjr.user_id = up.user_id
   AND bjr.end_time >= 1583821800000
   AND bjr.start_time <= 1583825400000
 WHERE up.available_for_survey = 1 
   AND upz.zip_code_id = 1
   AND bjr.user_id IS NULL

For further help with optimisation (i.e. which indexes to provide) we'd need SHOW CREATE TABLE statements for all relevant tables as well as the EXPLAIN for the above

Upvotes: 1

Related Questions