Optmizing MySQL GROUP BY or DISTINCT on large views

Question

Consider a view consisting of several tables... for example a v_active_car, which is made up of the tables car joined on to body, engine, wheels and stereo. It might look something like this:

v_active_cars view

SELECT * FROM car
    INNER JOIN body ON car.body = body.body_id
    INNER JOIN engine ON car.engine = engine.engine_id
    INNER JOIN wheels ON car.wheels = wheels.wheels_id
    INNER JOIN stereo ON car.stereo = stereo.stereo_id
 WHERE car.active = 1
 AND engine.active = 1
 AND wheels.active = 1
 AND stereo.active = 1

Each component of the car has an "active" flag. Now, I need to find all the stereos that are available in active cars. To do this in need to use the whole view, not just the stereo table - just because a stereo is active doesn't mean it's available in a car.

So I can do

SELECT DISTINCT stereo_id FROM v_active_cars

Even though this may return a very small number of rows, it's stil a very slow query.

I've tried this, but it's even slower:

SELECT stereo_id FROM stereo WHERE EXISTS
(SELECT 1 FROM v_active_cars WHERE stereo_id = stereo.stereo_id)

Is there anything else I could do to make this faster?

Answer 1

You seem to be doing everything right. The next step would be checking index coverage.

Answer 2

Try this:

SELECT stereo_id
FROM stereo s, (
  SELECT *
  FROM v_active_cars
  ORDER BY stereo_id
  ) v
WHERE s.active = 1
  AND v.stereo = s.stereo_id

ORDER BY here should prevent pushing predicate into the view, and the optimizer should select a hash join.

Answer 3

You can try creating a view for each part showing only the active ones and then join to those. eg.

VIEW activeCar
SELECT * FROM car WHERE car.active = 1

VIEW activeEngine
SELECT * FROM engine WHERE engine.active = 1

Then your final view can be

SELECT * FROM activeCar
INNER JOIN activeEngine ON activeCar.engine = activeEngine.engine_id

Obviously make sure you have an index on the active column.

Another alternative is to have an index on both the id and the active flag. You can then perform the active=1 when joining. This way only one index is used to join rather than one for the id and one for active.

SELECT * FROM car
INNER JOIN body ON car.body = body.body_id AND body.active = 1
INNER JOIN engine ON car.engine = engine.engine_id AND engine.active = 1
INNER JOIN wheels ON car.wheels = wheels.wheels_id AND wheels.active = 1
INNER JOIN stereo ON car.stereo = stereo.stereo_id AND stereo.active = 1

Answer 4

1. make sure that there are indexes for all the JOINs
2. in your case, each level is selected both by a key, and a flag. adding the flag as part of the index might allow the DB to use only the index, instead of reading the whole record
3. make sure you have enough RAM to hold the resultset. InnoDB tables in particular have lots of knobs that you have to tune. most of the defaults assume very old hardware and too little RAM.