Trzy stoły:

product: z kolumnami: ( a, g, ...a_lot_more... )

a: PK, clustered
g: bit-column

main: z kolumnami: ( c, f, a, b, ...a_lot_more... )

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

lookup z kolumnami: ( a, b, c, i )

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

Nie mogę znaleźć dobrych indeksów dla złączenia:

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

Wypróbowałem indeks pokrycia product (g, a, ...)i jest on używany, ale nie ze spektakularnymi wynikami.

Niektóre kombinacje indeksów w lookuptabeli tworzą plany wykonania ze scalaniem indeksów, z niewielkim wzrostem wydajności w porównaniu z poprzednim planem.

Czy brakuje mi oczywistej kombinacji?

Czy ponowne zaprojektowanie konstrukcji może pomóc?

DBMS to MySQL 5.5, a wszystkie tabele używają InnoDB.

Rozmiary stołu:

product: 67K   ,  g applied:    64K 

main:   420K   ,  f applied:   190K

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

To mnie boli ...

Wcześniej musiałem używać tabel tymczasowych z InnoDB. Załaduj je z filtrami, utwórz indeks, dołącz do tabeli temp.

Problem, jak sądzę, polega na tym, że InnoDB ma tylko algorytm Nested Join: dorosłe optymalizatory zapytań RDBMS mają więcej do wykorzystania. Jest to oparte na próbie uruchomienia ładowań typu Data Warehouse na InnoDB.

Tabele temp zmniejszają ogólną złożoność w dół poziomu optymalizatora zapytań MySQL ...

Wygląda jak produkt kartezjański. Powtórz kryteria DOŁĄCZ

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

SUGESTIA ALTERNATYWNA

Może się to wydawać niekonwencjonalne i prawdopodobnie pachnie jak SQL Anitpattern, ale oto idzie ...

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

Nie przesunąłem product.g = 1i main.f = 1do podkwerend, ponieważ są to pola bitowe i po prostu wykonam skanowanie tabeli w tym momencie. Nawet gdyby pola bitowe były indeksami, Optymalizator zapytań po prostu zignorowałby taki indeks.

Oczywiście możesz zmienić SELECT * FROM lookupna, SELECT a FROM lookupjeśli twój SELECT nie potrzebuje niczegolookup

Być może włącz a, b do JOIN między wyszukiwaniem a głównym, jeśli ma to sens

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

lub odłóż c i połącz trzy kolumny (Indeks trzech kolumn w maini 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