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 (IMG:http://forum.php.pl/style_emoticons/default/winksmiley.jpg)

dzieki za odpowiedz (IMG:http://forum.php.pl/style_emoticons/default/smile.gif)

[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 (IMG:http://forum.php.pl/style_emoticons/default/winksmiley.jpg) 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 (IMG:http://forum.php.pl/style_emoticons/default/winksmiley.jpg)

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 (IMG:http://forum.php.pl/style_emoticons/default/winksmiley.jpg) 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 (IMG:http://forum.php.pl/style_emoticons/default/smile.gif)

[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 (IMG:style_emoticons/default/winksmiley.jpg)

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 (IMG:style_emoticons/default/smile.gif)

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 (IMG:style_emoticons/default/smile.gif)

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

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