Hi, I have a database where one of my tables (Adverts) are requested a LOT. It's a relatively narrow table with 12
columns,but the size is growing pretty rapidly. The table is used i relation to another one called (Car), and in the
formof "cars has many adverts". I have indexed the foreign key car_id on Adverts.
However the performance when doing a "SELECT .* FROM cars LEFT OUTER JOIN adverts on cars.id = adverts.car_id WHERE
cars.brand= 'Audi'" is too poor. I have identified that it's the Adverts table part that performs very bad, and it's by
farthe biggest of the two. I would like to optimize the query/index, but I don't know if there at all is any
theoreticaloption of actually getting a performance boost on a join, where the foreign key is already indexed?
One idea I'm thinking of my self is that I have a column called state on the adverts which can either be 'active' or
'deactivated'.The absolute amount of 'active adverts are relatively constant (currently 15%) where the remaining and
growingpart is 'deactivated'.
In reality the adverts that are selected is all 'active'. I'm hence wondering if it theoretically (and in reality of
cause)would make my query faster if I did something like: "SELECT .* FROM cars LEFT OUTER JOIN adverts on cars.id =
adverts.car_idWHERE cars.brand = 'Audi' AND adverts.state = 'active'" with a partial index on "INDEX adverts ON
(car_id)WHERE state = 'active'"?
Regards Niels Kristian