I have below table structure where one department can have many employees.
Department
id
dept_name
...
Employee
id
emp_name
dept_id(foreign key to department table)
....
Consider I have 30 departments and each department has 10k records.
Now I fire below query
select * from department dept, Employee emp where emp.dept_id=dept.id and dept.id=10;
My question is there any benefit on creating index on foreign key column dept_id of table Employee ?
Per my understanding, there should be benefit of creating index on column dept_id as without index DBMS has to scan all records of Employee table to find dept_id=10. If there is index then it can quickly find the range of record having dept_id=10 with the help of non clustered index.