0

I have created two tables having one to many relationship.

Team :- 

CREATE TABLE TEAM( ID INT(5) NOT NULL AUTO_INCREMENT, NAME VARCHAR(6) NOT NULL, PRIMARY KEY(ID) )ENGINE=INNODB;

Member :- 

CREATE TABLE MEMBER ( ID INT(5) NOT NULL AUTO_INCREMENT, NAME VARCHAR(6) NOT NULL, TEAM_ID INT(5) NOT NULL, PRIMARY KEY (ID), KEY TEAM_ID (TEAM_ID), CONSTRAINT MEMBER_ibfk_1 FOREIGN KEY (TEAM_ID) REFERENCES TEAM (ID) ) ENGINE=InnoDB

One team can have many members.

Data inside both tables:-

Team Table

ID NAME 1 TEAM 1 2 TEAM 2 Member Table:- ID NAME TEAM_ID 1 M1 1 2 M2 1 3 M3 1 4 M4 1 5 M5 1 6 M6 1 7 M7 1 8 M8 1 9 M9 2 10 M10 2 11 M11 2 12 M12 2

Team 1 is having 8 members & Team 2 is having 4 members.

Now I am executing below join query to fetch team & members details.

SELECT * FROM TEAM JOIN MEMBER ON TEAM.ID = MEMBER.TEAM_ID WHERE TEAM.ID = 1

When I am explaining a plan of a query I am getting below output.

id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE TEAM const PRIMARY PRIMARY 4 const 1 \N 1 SIMPLE MEMBER ALL TEAM_ID \N \N \N 12 Using where

I have already given index on foreign key but still mysql is scanning all rows of member table ? Why such ?

I am using 5.6.12 version of mysql.

Please help.

Improve this question

1 Answer 1

Reset to default
4

Mysql considers to make full scan instead of reading the index better because most of the values in the index has value 1. If it decides to scan index and fetch data from it then it will require to make additional lookup to fetch real data from table. It's not optimal way. Try to create a third team with one member only and in this case mysql must use index. Mysql will start using the index as your db will grow and more unique data will be stored in TEAM_ID in comparison to whole data. It's called index cardinality. See explanation here What is cardinality in MySQL?.

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

Have added one team & member then tried to fetch newly added member then only one is scanned but for team 1 it is still scanning all rows of table.why such ?
I have inserted more record into table & it has started scanning matching row instead of all row .. Do you have any reference regarding this answer ?
You should search for index cardinality. One of the source is webmonkeyuk.wordpress.com/2010/09/27/…
Read about the EXPLAIN output format. Another reason for MySQL to use full table scan instead of index scan for small tables is the use of * in SELECT. Since it needs to read values that are not present in the index (because of *) and the table is small, it saves some disk access by skipping the index. The situation changes when the table grows.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.