When joining database tables, index tables can greatly improve performance. Unless the proper indexes are available, queries that use joins can take a long time.
Assume you have the following Select statement:
SELECT * FROM dept, emp WHERE dept.dept_id = emp.dept_id
In this example, the dept and emp database tables are being joined using the dept_id field. When the driver executes a query that contains a join, it processes the tables from left to right and uses an index on the second table’s join field (the dept field of the emp table). To improve join performance, you need an index on the join field of the second table in the FROM clause.
If the FROM clause includes a third table, the driver also uses an index on the field in the third table that joins it to any previous table. For example:
SELECT * FROM dept, emp, addr WHERE dept.dept_id = emp.dept AND emp.loc = addr.loc
In this case, you should have an index on the emp.dept field and the addr.loc field.
