CODEWITHSUNDEEP
mysqlindexing
Navnath
Navnath

Reputation: 133

How Mysql indexes works in case of JOIN?

Suppose I have two tables patient, person Mysql query is like below.

select fname , lname 
from patient p 
left join per on (per.person_id=p.person_id) 
where p.account_id=2 and (per.fname like  'will%'  OR per.lname like 'will%' ).

In case of this query how mysql will use index created on (p.account_id,p.person_id)

person_id is a foreign key from person table in patient table. .

Upvotes: 0

Views: 40

Answers (1)

Rick James
Rick James

Reputation: 142316

I suspect you do not want LEFT. With LEFT JOIN, you are asking for account #2 whether or not he is named 'will'.

SELECT fname, lname
    FROM patient p
    JOIN per ON per.person_id = p.person_id
    WHERE p.account_id = 2
      AND (per.fname LIKE 'will% OR per.lname LIKE 'will%')

will find the full name of account #2 if it is a 'will', else return nothing.

You have not said what indexes you have, so we cannot explain your existing indexes. Please provide SHOW CREATE TABLE for each table.

For either version of the query, these indexes are the only useful ones:

p:  INDEX(account_id)  -- if it is not already the PRIMARY KEY
per:  INDEX(person_id)  -- again, if it is not already the PRIMARY KEY.

A PRIMARY KEY is a UNIQUE index.

The first index (or PK) would be a quick lookup to find the row(s) with account_id=2. The second would make the join work well. No index is useful for "will" because of the OR.

The query will look at patient first, then per, using "Nested Loop Join".

Please also provide EXPLAIN SELECT ..., so we can discuss the things I am guessing about.

Upvotes: 1

Related Questions