Optimization problem: compound clustered keys, flag conditions and index-merge

Question

Three tables:

product: with columns: ( a, g, ...a_lot_more... )

a: PK, clustered
g: bit-column

main: with columns: ( c, f, a, b, ...a_lot_more... )

c: PK, clustered
f: bit-column
(a, b): UQ 

lookup with columns: ( a, b, c, i )

(a, b): PK, clustered
a: FK to product(a)
c: UQ, FK to main(c)
i: bit-column

I can't find good indexes for the join:

FROM  
    product
  JOIN 
    lookup
      ON  lookup.a = product.a  
  JOIN
    main
      ON  main.c = lookup.c 
WHERE 
      product.g = 1
  AND
      main.f = 1
  AND 
      lookup.i = 1
  AND lookup.b = 17

I tried a covering index on product (g, a, ...) and it's used but not with spectacular results.

Some combinations of indexes on the lookup table produce execution plans with index-merge, with slight efficiency gain over the previous plan.

Is there some obvious combination that I am missing?

Could a re-design of the structure help?

The DBMS is MySQL 5.5 and all tables are using InnoDB.

---

Table sizes:

product: 67K   ,  g applied:    64K 

main:   420K   ,  f applied:   190K

lookup:  12M   ,  b,i applied:  67K 

Answer 1

It looks like a Cartesian product. Redo the JOIN Criteria

FROM  
    product
  JOIN 
    lookup
      ON  product.a = lookup.a  
  JOIN
    main
      ON  main.c = lookup.c 
WHERE 
      product.g = 1
  AND
      main.f = 1
  AND 
      lookup.i = 1
  AND lookup.b = 17

ALTERNATE SUGGESTION

This may seem unorthodox and probably smells like SQL Anitpattern, but here it goes...

FROM  
    product
JOIN 
    (
        SELECT * FROM lookup
        WHERE i=1 AND b=17
    ) lookup ON product.a = lookup.a  
JOIN
   main ON main.c = lookup.c 
WHERE 
    product.g = 1 AND main.f = 1

I did not move the product.g = 1 and main.f = 1 into subqueries because they are bit fields and will just do a table scan at the point. Even if the bit fields were indexes, the Query Optimizer would simply ignore such an index.

Of course, you could change SELECT * FROM lookup to SELECT a FROM lookup if your SELECT does not need anything from lookup

Perhaps involve a,b in the JOIN between lookup and main if this makes sense

FROM  
    product
  JOIN 
    lookup
      ON  product.a = lookup.a  
  JOIN
    main
      ON  main.a = lookup.a AND main.b = lookup.b
WHERE 
      product.g = 1
  AND
      main.f = 1
  AND 
      lookup.i = 1
  AND lookup.b = 17

or put back c and join on three columns (Index on the three columns in main and lookup)

FROM  
    product
  JOIN 
    lookup
      ON  product.a = lookup.a  
  JOIN
    main
      ON main.a = lookup.a
      AND main.b = lookup.b
      AND main.c = lookup.c
WHERE 
      product.g = 1
  AND
      main.f = 1
  AND 
      lookup.i = 1
  AND lookup.b = 17

Answer 2

This pains me...

I've had to use temp tables with InnoDB before. Load them with filters, create an index, join these temp table.

The problem as I reckon is if that InnoDB only has Nested Join algorithm: the grown-up RDBMS query optimisers have more to use. This is based on trying to run Data Warehouse type loads on InnoDB.

Temp tables drags the overall complexity down the MySQL query optimiser's level...