CODEWITHSUNDEEP
sql
Karthikeyan
Karthikeyan

Reputation: 319

SQL query performance in JOIN

I am working in SQL and has 26000 thousands of records in the primary key sql table.

Employees Table data: 

    Id | Name | DepartmentId
   --------------------------
    1  | AA   |    1
    2  | BB   |    1
    3  | CC   |    2

Departments table data:

Id  | Name     |
----------------
1   |  IT      |
2   |  Network |

Created index for Employee.DepartmentId

I have a query as below,

Select E.Id, E.Name, E.DepartmentId
from Employees E
JOIN Department D
ON E.DepartmentId = D.Id

The above query executes in 2 seconds and returned all the records. but when I included the department name in the select list it takes more time around 10 seconds.

Select E.Id, E.Name, E.DepartmentId,D.Name
from Employees E 
JOIN Department D
ON E.DepartmentId = D.Id

What is the issue here?

Thanks in advance.

Upvotes: 2

Views: 99

Answers (2)

Willem van Ketwich
Willem van Ketwich

Reputation: 5994

As alluded to by Gordon, it is not the join on the tables with the indexes that are the issue, rather the volume of data being returned in the result set (from the additional Department Name) that is either being displayed in the terminal or in the gui.

Additionally, you should also be able to use the EXPLAIN keyword in MySQL to help identify any index issues.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271231

For this query:

select E.Id, E.Name, E.DepartmentId, D.Name
from Employees E join
     Department D
     on E.DepartmentId = D.Id;

The optimal index is Department(Id, Name). You may already have essentially this index if Department(Id) is the primary key.

Perhaps a more important issue is the size of the result set. The department name may be large (particularly if it is declared as a char() rather than a varchar()). The issue may simply be the size of the department name.

Upvotes: 1

Related Questions