I'm having an performance issue with a query, the query is something like:

Select * 
from requests 
where start_date > {start_date} 
and end_date < {end_date} 
and employee in {employee_id_list}

The problem is that this employee list is huge, and come from another service and could contain up to 10.000 employee_id.

Are there any better way of doing this than using the employee_id_list as parameter for this query?

  • 1. What version of MySql 2. What is the primary key 3. What are any secondary indexes defined? – bbaird Sep 3 at 19:41

try use "with" clase

with employees as (
--- your employee list in select statement 
--- for example:
select 1 emp_id
select 2 

select * 
from requests rq
join employees em on (em.emp_id = rq.employee)
where start_date > {start_date} and end_date < {end_date}
| improve this answer | |
  • I will try this out, I didn't knew this way could make any difference. – Luis Tellez Sep 3 at 8:55
  • join is faster than in clause especially if you have unique list – areklipno Sep 3 at 11:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.