Optymalizacja selecta, czy to juz kres mozliwosci??? Opcje

[abusiek]

Witam!

Od paru godzin optymalizuje zapytanie do dwoch tabel, w jednej (t1) jest okolo 120000 rekordow w drugiej (t2) ok 2400000.
t1 jest w relacji jeden do wielu z t2, na jeden wiersz z t1 przypada okolo 30, 40 rekordow w t2.
Pytanie czy troche ponad 4s to szczyt mozliwosci mysql-a w takich przypadkach??

[wookieb]

Jeżeli masz założone odpowiednie indexy i wszystko zoptymalizowane to raczej tak.

[abusiek]

to mam problem

dzieki za odpowiedz

[bigZbig]

Pytanie jak wygląda zapytanie ?

Ten post edytował bigZbig 19.02.2009, 15:54:35

[abusiek]

[SQL] pobierz, plaintext 
SELECT DISTINCT t1.id FROM t1, t2 WHERE t1.type = 'typ' AND t1.id = t2.t1_id AND t2.isLast = 1 AND (t2.name = 'pole1' AND (ucase(t2.value) LIKE ucase('%costam%') OR ucase(t2.value) LIKE ucase('%costam%') OR ucase(t2.value) LIKE ucase('%costam%') OR ucase(t2.value) LIKE ucase('%costam%') ))
[SQL] pobierz, plaintext 

Zapytania wygladaja jak wyzej z ze warunkow po na pola value i name moze byc wiecej. indeksy sa na t1.type, t2.t1_id

Ten post edytował abusiek 19.02.2009, 16:29:12

[sowiq]

Zapytania typu

[SQL] pobierz, plaintext 
SELECT * FROM tabela1, tabela2 WHERE tabela1.id = tabela2.id
[SQL] pobierz, plaintext 

nie są dobrym pomysłem, a już na pewno nie przy takiej ilości rekordów.
Zapytanie takie działa w prosty sposób:
1. tworzy iloczyn kartezjański obu tabel (czyli każdy z każdym)
2. wybiera z tego iloczynu pary spełniające warunek
Czyli jeżeli masz dwie tabele po 1k rekordów, a warunek spełnia tylko jedna para, to i tak najpierw będzie stworzonych milion par, a dopiero wybrany jeden wynik.
Rozwiązanie - JOIN. Poczytaj, na pewno przyspieszysz działanie.

Dalej. where t1.type = 'typ' Dużo szybciej działa porównywanie typu INT niż VARCHAR. Powinieneś te typy zapisywać jako liczby, a jeżeli nie możesz tego zmienić, to nawet stwórz dodatkowe pole typu INT, załóż na niego index i wstaw wartości odpowiadające typom (jakoś to sobie ustal).

[SQL] pobierz, plaintext 
ucase(t2.value) LIKE ucase('%costam%')
[SQL] pobierz, plaintext 

Jeżeli masz kodowanie bazy inne niż binarne (a powinieneś w tym przypadku), to nie ma konieczności stosować ucase(), bo wtedy baza jest nieczuła na wielkości znaków.

Tyle mi się nasunęło na obecną chwilę.

Ten post edytował sowiq 19.02.2009, 16:31:49

[abusiek]

Zastosowalem sie do twoich rad i zapytanie wyglada tak

[SQL] pobierz, plaintext 
SELECT DISTINCT d.id FROM d1 d INNER JOIN decF df ON d.id = df.documentId WHERE d.listing_id = '54' AND df.isLast = 1 AND ( (df.name = 'applicationDate' AND UCASE(df.value) LIKE UCASE('%adrian%') ) OR (df.name = 'automat' AND UCASE(df.value) LIKE UCASE('%adrian%') ) OR (df.name = 'date' AND UCASE(df.value) LIKE UCASE('%adrian%') ) OR (df.name = 'from' AND UCASE(df.value) LIKE UCASE('%adrian%') ) OR (df.name = 'To' AND UCASE(df.value) LIKE UCASE('%adrian%') ) OR (df.name = 'place' AND UCASE(df.value) LIKE UCASE('%adrian%') ) OR (df.name = 'point' AND UCASE(df.value) LIKE UCASE('%adrian%') ) OR (df.name = 'point1' AND UCASE(df.value) LIKE UCASE('%adrian%') ) )
[SQL] pobierz, plaintext 

Czyli dodalem kolumne rownowazna dla type z poprzedniego posta z typem int i zastosowalem inner join. Zapytanie wykonuje sie ciut szybciej ale dalej nie schodzi ponizej 4s.

To co mi pokazuje explain:

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE d ref PRIMARY,listing_id_dx listing_id_dx 5 const 30184 Using where; Using temporary
1 SIMPLE df ref document_index document_index 5 d.id 23 Using where; Distinct

[patryczakowy]

a spróbuj LEFT JOIN LUB RIGHT JOIN zdaje się że ze wszystkich join one są najszybsze

[abusiek]

sa najszybsze ale mnie interesuja tylko wiersze dopasowane z obu stron wiec i left i right join odpada

[patryczakowy]

ale jak okaże się szybsze to możesz puste wiersze z poziomu php wyeliminować sprawdź może gra jest warta świeczki

[abusiek]

sprawdzilem nie ma zadnej roznicy

[phpion]

Ja bym problemów szukał w użyciu DISTINCT. Potrafi on znacząco spowolnić zapytanie. Dla sprawdzenia: wykonaj sobie to samo zapytanie ale bez klauzuli DISTINCT. W rezultacie otrzymasz pewnie dużo więcej rekordów ale czas wykonywania zapytania powinien być niższy od aktualnego. Rozumiem, ze DISTINCT jest Ci koniecznie potrzebne - wówczas pomyślimy co z tym fantem zrobić.

[abusiek]

rzeczywiscie jest koniecznie potrzebne, ale przy zapytaniu ktore dalem wyzej tylko bez distinct przyspieszylo o jakies 0.3s, czyli nieduzo

Z tego co sie orientuje w mysql-u to mozna przyspieszyc distinct przy pomocy loose index scan, ale w moim przypadku nie bardzo da sie go zastosowac bo trzeba by przede wszystkim zlamac normalizacje bazy no i w indexie musialalby byc kolumna value ktora jest typu text a mysql ma ograniczenia na indeksy

[dr_bonzo]

Ja bym sie upatrywal problemow w "LIKE '%....%'" (nie skorzysta z indexow na tym polu, ale... jesli wczesniej wybierze tylko te 20-30 rekordow to nie powinien byc problem)

Wyrzuc je wszystkie i sprawdz czas.
Dodaj tylko jeden i znow sprawdz.

Ten post edytował dr_bonzo 19.02.2009, 18:47:59

[sowiq]

Tak jak pisałem wyżej - pousuwaj UCASE(). MySQL nie jest wrażliwy na wielkość znaków. No chyba, że używasz kodowania binarnego, np. utf8_bin, latin2_bin itp. Ale jeżeli masz utf8_general_ci, utf8_polish_ci, czy inne bez bin w nazwie, to MySQL nie będzie rozróżniał wielkości znaków.
Sprawdź.

[abusiek]

Zapytanie przeszukuje te dwie tabelki wiec uzywanie like('%%') ani ucase nie jest opcjonalne tak naprawde. mam szukac czy w value zawiera sie taka fraza i nie zwracac uwagi na wielkosc liter. Chyba nie ma na to innego sposobu....

[dr_bonzo]

A mozesz dac strukture bazki + troche przykladowych rekordow (tzn. gotowiec w SQL) to bedziemy sie mogli pobawic praktycznie?

[abusiek]

dzieki za taki duzy odzew. Danych nie moge udostepnic bo mnie pracodawca zlinczuje, znaczy poufne sa Ale postaram sie jutro wrzucic strukture bazy i jakis skrypt ktory wypelni ja danymi. Tych danych musi byc naprawde duzo bo czasy ktore wam podaje sa przy zapytaniu w ktorym dla pierwszej tabelki pasuje ok. 30000 wierszy. Przy innych typach (po pare tysiecy wierszy jest juz duzo lepiej)... W kazdym razie dzieki za taki duzy odzew

[sowiq]

mam szukac czy w value zawiera sie taka fraza i nie zwracac uwagi na wielkosc liter.

Sorry, ale chyba nie przeczytałeś tego co napisałem. Powtórzę zatem kolejny, trzeci raz:

MySQL przy wyszukiwaniu nie zwraca uwagi na wielkość liter. Możesz dać LIKE '%coś%', a warunek będzie spełniony dla COŚ, cOś, coŚ itd.

Wyjątkiem jest binarne kodowanie tabel/pól - napisałem o tym powyżej.

[yevaud]

odpowiem troche po czasie bo wlasnie sam natrafilem na podobny problem.

Zakladam ze uzywasz silnika InnoDb, jesli nie to mozesz od razu czytac koniec

zrob dodatkowa tabele na silniku MyIsam ktora bedzie miala aktualizowane dane tekstowe(te na ktorych wykonujesz zapytanie like), oraz primary identyfikator. Dane mozesz aktualizowac triggerami lub w dowolnie wybrany sposob np. procedura skladowana odpalana w cronie (zalezy od konkretnej sytuacji co ma najwiecej sensu, najmniej obciaza baze, czy wyszukiwanie ma byc na aktualnych danych itp)

na pola tekstowe mozesz wtedy zalozyc indeks Fulltext i wtedy...

http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html

edit:
Jesli chodzi o proby optymalizacji poprzez zamiane where t1.id = t2.id na rozne join to tutaj akurat taki warunek jest rownoznaczny z inner join(wtedy niewazne czy left czy right), a szybszego joina nie ma niezaleznie od tego jak go w sql zapiszemy

edit2:
moje zapytanie wykonywalo sie srednio ~5sec
w tym momencie nie przekracza 0.01s

Ten post edytował yevaud 22.10.2009, 13:39:05