Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 
share|improve this question
    
Try moving the filter predicates into the joins and see if the optimiser does something sensible with that. I've seen SQL Server's optimiser fail on that before. –  ConcernedOfTunbridgeWells Aug 3 '12 at 11:09
    
Looks like a Cartesian product because I do not see anything JOINing from the product table. Or did I miss something ??? –  RolandoMySQLDBA Aug 3 '12 at 12:02
    
@RolandoMySQLDBA: You are right. I'll correct the query. –  ypercube Aug 3 '12 at 12:05

2 Answers 2

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
share|improve this answer
    
Thnx. Different EXPLAIN plan, but similar performance. –  ypercube Aug 3 '12 at 12:13
    
What the cardinality of the main.f and product.g ??? If the cardinality of main.f and product.g for the value is 1 is less than 5% of the table rows, an index on main.f and product.g may be justifiable. –  RolandoMySQLDBA Aug 3 '12 at 12:14
    
Never mind, they are indexed already. If the cardinality of main.f and product.g is 2, you could ditch those indexes. –  RolandoMySQLDBA Aug 3 '12 at 12:17
    
Edited the question with table sizes and rows used (after the conditions are applied). –  ypercube Aug 3 '12 at 12:19
    
I updated my question, suggestion JOINing on a,b instead of c. See if that makes a different EXPLAIN plan –  RolandoMySQLDBA Aug 3 '12 at 12:25

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...

share|improve this answer
    
Thnx, I'll try that. The number or rows (after the criteria are applied are not that big, 64K, 67K, 190K respectively). Maybe I should try to get rid of one of the three tables (main) by denormalizing data into lookup? –  ypercube Aug 3 '12 at 12:08
    
@ypercube: denormalising will make rows wider, lower page density = other problems –  gbn Aug 3 '12 at 12:52

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.