Alex Alex - 4 months ago 45
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;

Given that the tables are big, I need to use indexes for both
, and obviously for the join condition
. 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!!!


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.