Optymalizacjaj zapytania SELECT z wieloma OR

0

zastanawiam sie czy da sie zoptymalizowac zapytanie jak ponizej;
baza MySOL InnoDB (ok 2GB)
tabela p_zlec zawiera zlecenia (300 tys rekordow ok 200MB)
tabela p_firma zawiera firmy (150 tys rekordow ok 100MB)
tabela p_firmadr zawiera adresy firm (170 tys rekordow ok 70MB)
indeksy pomiedzy tabelami pozakladane (INT11)

opisowo o co chodzi: w zleceniach ta sama firma moze wystepowac jako zleceniodawca i/lub miejsce zaladunku itd - w sumie w 5 polach; czasami trzeba znalezc wszystkie zlecenia na jakich wystepuje dana firma niezaleznie w jakiej roli wystapowala; wyszukujemy po nazwie, ktora moze byc nazwa pelna, krotka lub NIP'em firmy z tabeli p_firma

uwaga: powiazania miedzy tabelami robie z przyzwyczajenia w WHERE a nie przez JOIN bo nic to nie zmienia wydajnosciowo (tak mi wyszlo w testach)

testy: zapytanie, w ktorym szukamy firmy w zleceniach w

  • 1 wybranym polu np. gdy byla tylko zleceniodawca trwa ok 1 sek,
  • w 2 polach np. gdy byla zleceniodawca i/lub miejscem zaladunku 30 sek
  • we wszystkich 5 polach ponad 60 sek
    czy mozna to jakos przebudowac zeby szybciej dzialalo dla wyszukiwania po 2-5 polach? zapytanie dla przypadku wyszukiwania po 5 polach
SELECT 
	zlec_id 
FROM 
	p_zlec AS z 
    , p_firma AS fzle 
    , p_firmadr AS fazle
    , p_firma AS fzal 
    , p_firmadr AS fazal
    , p_firma AS fodb 
    , p_firmadr AS faodb
    , p_firma AS froz 
    , p_firmadr AS faroz
    , p_firma AS fpla 
    , p_firmadr AS fapla
WHERE 
		fzle.firma_id = fazle.firmadr_firma_id
    AND fazle.firmadr_id = z.zlec_firmadr_id_zleceniodawca
    AND fzal.firma_id = fazal.firmadr_firma_id
    AND fazal.firmadr_id = z.zlec_firmadr_id_zaladunek
    AND fodb.firma_id = faodb.firmadr_firma_id
    AND faodb.firmadr_id = z.zlec_firmadr_id_odbiorca
    AND froz.firma_id = faroz.firmadr_firma_id
    AND faroz.firmadr_id = z.zlec_firmadr_id_rozladunek
    AND fpla.firma_id = fapla.firmadr_firma_id
    AND fapla.firmadr_id = z.zlec_firmadr_id_platnik
    AND (fzle.firma_nazwa_dluga like 'firma_X%' OR 
			fzle.firma_nazwa_krotka like 'firma_X%' OR
			fzle.firma_nip like 'firma_X' OR
			fzal.firma_nazwa_dluga like 'firma_X%' OR 
			fzal.firma_nazwa_krotka like 'firma_X%' OR
			fzal.firma_nip like 'firma_X' OR
			fodb.firma_nazwa_dluga like 'firma_X%' OR 
			fodb.firma_nazwa_krotka like 'firma_X%' OR
			fodb.firma_nip like 'firma_X' OR
			froz.firma_nazwa_dluga like 'firma_X%' OR 
			froz.firma_nazwa_krotka like 'firma_X%' OR
			froz.firma_nip like 'firma_X' OR
			fpla.firma_nazwa_dluga like 'firma_X%' OR 
			fpla.firma_nazwa_krotka like 'firma_X%' OR 
			fpla.firma_nip like 'firma_X')
0

Ogólnie OR nie jest najlepszym rozwiązaniem pod kątem optymalizacyjnym. W dodatku masz LIKE co również nie jest najwydajniejsze. Na szybko z tego co widzę można by przerobić to zapytanie tak aby nie było OR'ów tylko rozbijasz je na zapytania z jednym warunkiem w oddzielne uniony czyli np tak:

SELECT 
    zlec_id 
FROM 
    p_zlec AS z 
    , p_firma AS fzle 
    , p_firmadr AS fazle
    , p_firma AS fzal 
    , p_firmadr AS fazal
    , p_firma AS fodb 
    , p_firmadr AS faodb
    , p_firma AS froz 
    , p_firmadr AS faroz
    , p_firma AS fpla 
    , p_firmadr AS fapla
WHERE 
        fzle.firma_id = fazle.firmadr_firma_id
    AND fazle.firmadr_id = z.zlec_firmadr_id_zleceniodawca
    AND fzal.firma_id = fazal.firmadr_firma_id
    AND fazal.firmadr_id = z.zlec_firmadr_id_zaladunek
    AND fodb.firma_id = faodb.firmadr_firma_id
    AND faodb.firmadr_id = z.zlec_firmadr_id_odbiorca
    AND froz.firma_id = faroz.firmadr_firma_id
    AND faroz.firmadr_id = z.zlec_firmadr_id_rozladunek
    AND fpla.firma_id = fapla.firmadr_firma_id
    AND fapla.firmadr_id = z.zlec_firmadr_id_platnik
    AND fzle.firma_nazwa_dluga like 'firma_X%'
union
SELECT 
    zlec_id 
FROM 
    p_zlec AS z 
    , p_firma AS fzle 
    , p_firmadr AS fazle
    , p_firma AS fzal 
    , p_firmadr AS fazal
    , p_firma AS fodb 
    , p_firmadr AS faodb
    , p_firma AS froz 
    , p_firmadr AS faroz
    , p_firma AS fpla 
    , p_firmadr AS fapla
WHERE 
        fzle.firma_id = fazle.firmadr_firma_id
    AND fazle.firmadr_id = z.zlec_firmadr_id_zleceniodawca
    AND fzal.firma_id = fazal.firmadr_firma_id
    AND fazal.firmadr_id = z.zlec_firmadr_id_zaladunek
    AND fodb.firma_id = faodb.firmadr_firma_id
    AND faodb.firmadr_id = z.zlec_firmadr_id_odbiorca
    AND froz.firma_id = faroz.firmadr_firma_id
    AND faroz.firmadr_id = z.zlec_firmadr_id_rozladunek
    AND fpla.firma_id = fapla.firmadr_firma_id
    AND fapla.firmadr_id = z.zlec_firmadr_id_platnik
    AND fzle.firma_nazwa_krotka like 'firma_X%' 
...

Oczywiście jest to tylko propozycja do sprawdzenia (po twojej stronie) bo też istotne jest jak są indeksy pozakładane, czy na poszczególnych zapytania faktycznie musimy łączyć n razy tą samą tabelę itd ...

, p_firma AS fzle 
    , p_firmadr AS fazle
    , p_firma AS fzal 
    , p_firmadr AS fazal
0
lechulysy napisał(a):

indeksy pomiedzy tabelami pozakladane (INT11)

Czy chodzi ci o klucze?

Like to jest zabójca wydajności, ale przy takie bazie nie powinno być większego problemu, chyba że to stoi na jakimś bieda serwerze.

0

@UglyMan: tak, klucze

4

Taka propozycja

with  p_firma_tpl as (
select firma_id form p_firma where  firma_nazwa_dluga like 'firma_X%' OR 
            fzle.firma_nazwa_krotka like 'firma_X%' OR      fzle.firma_nip like 'firma_X' OR
)
SELECT 
    zlec_id 
FROM 
    p_zlec, p_firma_tpl  where p_zlec.zlec_firmadr_id_zleceniodawca = p_firma_tpl.firma_id 
  or p_zlec.zlec_firmadr_id_zaladunek = p_firma_tpl.firma_id 
  or p_zlec..zlec_firmadr_id_odbiorca= p_firma_tpl.firma_id 
  or p_zlec.zlec_firmadr_id_rozladunek = p_firma_tpl.firma_id 
  or p_zlec.zlec_firmadr_id_platnik= p_firma_tpl.firma_id
0

@woolfik: to może być dobra droga - sprobuję jakoś to wpleść w mechanizm tworzenia zapytania bo oczywiscie jest wiele pol do filtrowania (firma to tylko jedno z kilkunastu dostepnych) oraz sortowania danych
@UglyMan: przy próbie uruchomienia wywala mi błąd #1064; moze to kwestia dosc leciwej wersji bazy 5.5.25a, czego na razie nie przeskocze

1

Wersja bez CTE:

SELECT 
    zlec_id 
FROM 
    p_zlec, (select firma_id form p_firma where  firma_nazwa_dluga like 'firma_X%' OR 
            fzle.firma_nazwa_krotka like 'firma_X%' OR      fzle.firma_nip like 'firma_X' OR) as  p_firma_tpl  where p_zlec.zlec_firmadr_id_zleceniodawca = p_firma_tpl.firma_id 
  or p_zlec.zlec_firmadr_id_zaladunek = p_firma_tpl.firma_id 
  or p_zlec..zlec_firmadr_id_odbiorca= p_firma_tpl.firma_id 
  or p_zlec.zlec_firmadr_id_rozladunek = p_firma_tpl.firma_id 
  or p_zlec.zlec_firmadr_id_platnik= p_firma_tpl.firma_id
0

obie propozycja zdecydowania poprawiły działanie :) natomiast latwiej mi zaimplementowac pomysl @UglyMan
bardzo dziekuję

0

Próbowałeś z UNION ALL? To może być czarny koń, ale nie jestem pewny ;)

1 użytkowników online, w tym zalogowanych: 0, gości: 1