Alex Alex - 11 days ago 8
SQL Question

Indexes in join with filter

Suppose the following SQL pseudocode:

select ... from A join B
on A.serial=27 and B.serial=34 and A.id=B.id;


Given that the tables are big, I need to use indexes for both
serial
, and obviously for the join condition
A.id=B.id
. Ideally, I need the filters applied before the join, as the result of the filters give much less rows than filter one table, then join, then filter second.

My question is: which combination of indexes would be fine for this situation? maybe two composites on A and B in the form
(serial id)
?

Thank you in advance!!!

Answer

This is Oracle, Not MySQL.
You don't need indexes for the id columns for join purposes, however -
it is a good idea to define ID columns as primary keys and this implicitly creates unique indexes

You do need indexes on the serial columns

Create index A_IX_SERIAL on A (SERIAL);
Create index B_IX_SERIAL on B (SERIAL);

P.s. How much is "much less rows"?
As much rows are return, it becomes a less good idea to use index.