Optymalizacja zapytania

Question

Cześć,

Mam takie zapytanie:

SELECT serieses_list.id,
(AVG(sr.story) + AVG(sr.graphics) + AVG(sr.music) + AVG(sr.characters)) / 4 AS middle
FROM serieses_list
JOIN serieses_tags ON serieses_tags.series_id = serieses_list.id
LEFT JOIN serieses_ratings AS sr ON sr.series_id = serieses_list.id
GROUP BY serieses_tags.series_id, serieses_tags.tag_id, sr.series_id, sr.user_id
HAVING SUM(IF(serieses_tags.tag_id = 1, 1, 0)) > 0
AND SUM(IF(serieses_tags.tag_id = 3, 1, 0)) > 0
AND SUM(IF(serieses_tags.tag_id = 6, 1, 0)) > 0
ORDER BY middle ASC
LIMIT 10 OFFSET 0

serieses_list

id

name

description

cover

type

episode_number

episode_length

emission_statuas

serieses_tags

id

tag_id

series_id

serieses_ratings

id	series_id	user_id	story	graphics	music	characters
1	16	1	8	7	9	6

Explain:

 

na ten moment w tabeli serieses_ratings jest ~28000 wierszy. Wykonanie tego zapytania trwa jakieś 1,5 sekundy. Co można zrobić żeby to jakoś zoptymalizować?

Edit: Poczytałem trochę o GROUP BY i udało mi się zbić czas wykonania do 900 ms

Answer 1

1 ) podaj strukture tabel ;-)

2 ) pomyśl czy nie mozna dodać indeksow

3 ) pokaz wynik wynik zapytania EXPLAIN

Mogę w ciemno powiedzieć ze tabela serieses_list nie jest ci potrzebna w zapytaniu bo nie pobierasz z niej żadnych danych oprócz ID do złaczenia. Może jesteś w stanie wykorzystać dwie tabeli i zrobić JOIN w taki sposób ?

ON serieses_tags.series_id = serieses_ratings.series_id

Zastanawia mnie też sama klauzula HAVING dziwnie dość wygląda. Napiszesz mi dlaczego jest zapisane to w taki sposób ?

Comments

Rzeczywiście, tabela serieses_list  jest tutaj zbędna, choć to nic nie zmieniło.

W edycji posta dodałem informacje o które prosiłeś.

---

@rafal612b,

Dlatego, że tylko w taki sposób jestem w stanie wyszukiwać wierszy na podstawie wielu tagów z innej tabeli.

---

hmmm można by też spróbować zrobić mniejsze pod zapytanie ;) Zamiast tego HAVING

SELECT series_id,

(AVG(sr.story) + AVG(sr.graphics) + AVG(sr.music) + AVG(sr.characters)) / 4 AS middle

FROM

( SELECT tag_id, series_id from serieses_tags WHERE serieses_tags.tag_id in [1,3,6])

LEFT JOIN serieses_ratings AS sr ON sr.series_id = series_id
GROUP BY serieses_tags.series_id, serieses_tags.tag_id, sr.series_id, sr.user_id

ORDER BY middle ASC

LIMIT 10 OFFSET 0

PS. nie jestem pewny czy dobrze napisałem podzapytanie bo dawno tego nie pisałem ;)

---

Must have w twoim problemie jest dodanie indeksów z tego co widać po EXPLAIN tabelka serieses_tags nie ma żadnych posible_index dodaj dwa indeksy do tabeli serieses_tags Pierwszy na dwie kolumny (tag_id, series_id) a drugi odwrotny ( series_id, tag_id). Sprawdź czy przyśpieszy ;) Potem EXPLAIN powie ci który indeks jest lepszy i który jest używany.

---

Może to pytanie zabrzmi głupio, ale o jakie indexy chodzi?

---

Dobra, udało mi się znacznie zbić czas wykonania tego zapytania.

Generalnie kod który podałeś zmienił logikę tego zapytania, ale na jego podstawie napisałem to:

SELECT DISTINCT(s.series_id),
(AVG(sr.story) + AVG(sr.graphics) + AVG(sr.music) + AVG(sr.characters)) / 4 AS middle
FROM
(
	SELECT series_id, tag_id
	FROM serieses_tags
	GROUP BY series_id
	HAVING SUM(CASE WHEN tag_id = 1 THEN 1 ELSE 0 END) > 0
	AND SUM(CASE WHEN tag_id = 3 THEN 1 ELSE 0 END) > 0
	AND SUM(CASE WHEN tag_id = 6 THEN 1 ELSE 0 END) > 0
) AS s
JOIN serieses_list ON serieses_list.id = s.series_id
LEFT JOIN serieses_ratings AS sr ON sr.series_id = s.series_id
GROUP BY s.series_id, s.tag_id, sr.series_id
ORDER BY middle DESC

Ten kod nie dość, że działa to jego wykonanie trwa 0.032s.