Optymalizacaja zapytania Opcje

[GameMaker]

Witajcie !

Poprawiam wyszukiwarkę portalową.
Problem tkwi w tym, że w godzinach szczytu, że zapytanie zaczyna zajeżdżać serwer.

Zapytanie, które jest aktualnie (przykład):

[SQL] pobierz, plaintext 
SELECT SQL_CALC_FOUND_ROWS SQL_CACHE post.id, post.autor, post.date AS newsdate, short_story AS story, post.xfields AS xfields, title, descr, keywords, category, alt_name, comm_num AS comm_in_news, allow_comm, rating, flag, editdate, editor, reason, view_edit, count( search_wordmatch.news_id) AS count FROM search_wordlist, search_wordmatch, post WHERE ( search_wordlist.id = search_wordmatch.word_id AND search_wordmatch.news_id = post.id ) AND ( search_wordlist.word_text = 'układ' OR search_wordlist.word_text = 'zamknięty' ) AND ( category != 13 AND category != 44 AND category != 45 AND category != 46 ) GROUP BY search_wordmatch.news_id ORDER BY count DESC, news_read DESC LIMIT 0,20
 
[SQL] pobierz, plaintext 

Przepisane zapytanie do innej postaci() :

[SQL] pobierz, plaintext 
SELECT SQL_CACHE count( search_wordmatch.news_id) AS kant ,post.id, post.short_story, post.title, post.autor, post.date, post.category, post.comm_num, post.alt_name, post.descr FROM `search_wordlist`
LEFT JOIN search_wordmatch ON search_wordlist.id = search_wordmatch.word_id 
LEFT JOIN post ON post.id = search_wordmatch.news_id WHERE (`category` NOT IN(13,44,45,35,46,53,70)) AND ( search_wordlist.word_text = 'Need' OR search_wordlist.word_text = 'for' OR search_wordlist.word_text = 'speed' ) 
GROUP BY news_id 
ORDER BY kant DESC ,  news_id DESC
LIMIT 0, 20 
[SQL] pobierz, plaintext 

Indexy :

Tabela :

-- search_wordlist

http://scr.hu/1s02/bg4fo

-- search_wordmatch

http://scr.hu/1s02/1fxpz

-- post

http://scr.hu/1s02/tm1s9


EXPLAIN tego zapytania :

http://scr.hu/1s02/ljnq1


Jakie są wasze sugestie odnośnie optymalizacji takiego zapytania.

[bpskiba]

No tak od początku.....
Odpuszczę sobie analizę indeksów i strukturę tabel (widać kolega wie co się z tym robi (IMG:style_emoticons/default/closedeyes.gif) ).
Odpalenie OPTIMIZE też pewnie jest jasne.
Pytanie o konfigurację serwera... Z mojego doświadczenia wynika, że olbrzymia ilość serwerów działa na ustawieniach domyślnych i zmiana tych ustawień rozwiązuje masę problemów.

Odnośnie meritum, czyli zapytania tutaj też jest olbrzymie pole do działania gdyż z mojego doświadczenia wynika, że silnik mysql pierwsze wykonuje złączenia i potem dopiero sortuje i grupuje złączone tabele - co siłą rzeczy wymaga mieszania dużą ilością danych. Stosując inne złączenia lub podzapytania nieraz uzyskiwałem stukrotny wzrost wydajności w stosunku do zapytań napisanych książkowo (IMG:style_emoticons/default/smile.gif)

przykład:
- książkowo

[SQL] pobierz, plaintext 
SELECT t1.a,t2.a 
FROM t1
JOIN t2 ON t1.a=t2.a
WHERE
t1.c =2
[SQL] pobierz, plaintext 

alternatywa 1

[SQL] pobierz, plaintext 
SELECT t1.a,t2.a 
FROM t1
JOIN t2 ON t1.a=t2.a AND t1.c=2
[SQL] pobierz, plaintext 

alternatywa 2

[SQL] pobierz, plaintext 
SELECT t3.a,t2.a 
FROM 
  (SELECT a FROM t1 WHERE c=2) AS t3
JOIN t2 ON t3.a=t2.a
[SQL] pobierz, plaintext 

Ten post edytował bpskiba 7.11.2013, 11:09:51

[GameMaker]

Serwer, aktualnie nie działa na domyślnej konfiguracji, zostało dużo zmian wprowadzonych pod kątem optymalizacji serwera przez administratora.

Dzięki za podpowiedź.

[redeemer]

'Using temporary; Using filesort' -> spróbój przenieść TMPDIR do pamięci RAM. Dodatkowo dorób mechanizm cacheowania w warstwie aplikacji.

Ten post edytował redeemer 7.11.2013, 11:27:24

[GameMaker]

Aktualnie testuję takie zapytanie :

[SQL] pobierz, plaintext 
SELECT SQL_CACHE post.id, short_story, title, autor, date, category,
                                                           comm_num,
                                                           alt_name,
                                                           descr ,
                                                           wyns.count AS trafnosc
FROM
  (SELECT news_id ,
          count(search_wordmatch.news_id) AS COUNT
   FROM
     (SELECT id
      FROM search_wordlist
      WHERE (search_wordlist.word_text = 'Układ'
             OR search_wordlist.word_text = 'zamknięty')) AS list
   JOIN search_wordmatch ON search_wordmatch.word_id = list.id
   GROUP BY news_id) AS wyns
JOIN post ON post.id = wyns.news_id
WHERE (`category` NOT IN(13,44,45,35,46,53,70))
ORDER BY `trafnosc` DESC
 
[SQL] pobierz, plaintext 

I powiem, że czas wykonywania zmniejszył się 2 x.

[bpskiba]

Z tego, co widzę to zagnieździłeś zapytania. Nie koniecznie o to mi chodziło, ale jak sam widzisz to daje efekty (IMG:style_emoticons/default/smile.gif)
Idea polega na tym, aby wybrać z tabeli to co jest potrzebne, a następnie łączyć ją z kolejną.



przenieś WHERE (`category` NOT IN(13,44,45,35,46,53,70)) do podzapytania

[GameMaker]

Zrobiłem mały update zapytania , ale przeniesienie where do podzapytania wyrzuca błąd.

[SQL] pobierz, plaintext 
 
SELECT SQL_CALC_FOUND_ROWS SQL_CACHE post.id, short_story, title, autor, date, category, comm_num, alt_name, descr , wyns.count AS trafnosc FROM (SELECT news_id ,count(search_wordmatch.news_id) AS count FROM (SELECT id, word_text FROM search_wordlist) AS list JOIN search_wordmatch ON search_wordmatch.word_id = list.id AND ( list.word_text = 'Arrow' ) GROUP BY news_id ) AS wyns JOIN dle_post ON post.id = wyns.news_id AND ( post.category NOT IN(13,44,45,35,46,53,70)) ORDER BY trafnosc DESC
[SQL] pobierz, plaintext 

bpskiba,

Chyba ,że miałeś inna rzecz na myśli.
Jako możesz to przytocz przykład.

Ten post edytował GameMaker 7.11.2013, 14:20:11

[bpskiba]

najpierw próbujesz grupować, a potem dołączasz kolejną tabelę - i to zapewne powoduje błąd.
Jeśli chodzi o przykład, to przeanalizuj to co napisałem jako 'alternatywa2', czyli pierwsze kwerenda wybierająca w podzapytaniu i następnie do tego dołączona druga tabela