CODEWITHSUNDEEP
mysqlsql
flyshell
flyshell

Reputation: 1

mysql inner join and where

2 tables: employee & employee_address as an example

three indexes are provided:

ALTER TABLE employee ADD INDEX `E_P` (`position`) USING BTREE;

ALTER TABLE employee_address ADD INDEX `EA_Z` (`zipcode`) USING BTREE;

ALTER TABLE employee_address ADD INDEX `EA_E` (`employee_id`) USING BTREE;

1st inner join

select * from employee_address ea
inner join employee e on e.employee_id = ea.employee_id and e.position = 'MANAGER'
where ea.zipcode > 30000

2nd inner join with where clause

select * from employee_address ea
inner join (
select * from employee e where e.position = 'MANAGER'
) e on e.employee_id = ea.employee_id 
where ea.zipcode > 30000

assume there are : 500000 records in each table 1000 different position 2000 different zipcode

I found query 1 is much more efficient.

What's different between this 2 queries?

Would it be possible to query it as fast as they were in just one table? and How?

select * from employee e where e.position = 'MANAGER' 
and e.zipcode > 30000

ALTER TABLE employee ADD INDEX `Z_P` (`zipcode`, `position`) USING BTREE;

Upvotes: 0

Views: 90

Answers (2)

elauser
elauser

Reputation: 106

On the second one you are prefiltering and then joining. This breaks the index. You are basically returning a new table on which to join which has no index.

If you look at the query explain plan you should see that the join in n2. does not have an index lookup.

It's generally a good idea, if you want to check what's slowing you down to use the query explain plan and learn to read what it is doing.

Upvotes: 1

ScaisEdge
ScaisEdge

Reputation: 133360

Looking to your code sample,instead of the index you proposed, you could try using these composite index

ALTER TABLE employee_address ADD INDEX ea_id_z (employee_id, zipcode) ;
ALTER TABLE employee ADD INDEX E_P (position, employee_id) ;

select * 
from employee_address ea
inner join employee e on e.employee_id = ea.employee_id 
and e.position = 'MANAGER'
    and ea.zipcode > 30000

In a query the db engine use a single index for each table so you should provide the index most selective and with much info needed during the where/join clause evaluation
Thne the use of proper composite index is most efficient

For a index in better add the most macthing column at left side (in this case position instead of zipcode because manager use a equal operator )

for the secondo query based on join with subquery the result of the subquery is managed as a temporary table the index are used for the inner select but are not useful for the join clause (in this case only the index for outer table can be used )

Upvotes: 0

Related Questions