Simple Query Help Needed

I have a table employee.

COLUMNS:
employee_id,
department_id,
office_id,
employee_name.

Indexes:
office_dept : department_id, office_id
dept : department_id
office : office_id
PRIMARY : employee_id

I want to get the employee that belongs to dept_id=1 & office_id=3 and also dept_id=2 & office_id=4.

The query I do,
SELECT employee_name FROM employee WHERE (department_id=1 AND office_id=3) OR (department_id=2 AND office_id=4);

This works perfectly fine. But the problem I have is for performance. The above query looks for all rows and ignores all of my indices for lookup and I know it is because of the "OR" in the WHERE clause. I know that there are just 4 rows that would match but still it looks up all the 16 rows instead and gives me 4 rows as results. This will cause issues when I launch this in the production environment because then I would have probably 100 thousands of such records. Please let me know how to make sure that the above query uses the proper index that is "office_dept" and looks up only 4 rows instead of all rows in the db!!!

 

Try this

select employee_name,
case when department_id = 1 and office_id = 3 then 'grp1'
when department_id = 2 and office_id = 4 then 'grp2'
else 'grp3'
end as group
from employee
where department_id = 1 or department_id = 2 or office_id = 3 or office_id
= 4

Even though this may( dep[ending upon the values in yr table) pull up more than 4 rows, but definitely will not scan entire table to fetch results.

While parsing the output, you can check for the field group and see if the value is grp1 or grp2 or grp3

grp1 is yr first scenario.

grp2 is second scenario

grp3 just discard, you dont want these.

Let me know if you need any more help.

Thanks

Lavee