[optymalizacja] Select Where IN( Select) Opcje

[mkozak]

Czesc miszcze od MySql-a,

Mam zagwostkę i pytanie. Here is situation.

Zapytanie :

[SQL] pobierz, plaintext 
SELECT count( id ) FROM stats WHERE serviceid
IN ( SELECT id FROM services WHERE name LIKE 'wp%' GROUP BY id)
[SQL] pobierz, plaintext 

Tabela stats ma około 70 MB i szczerze powiedziawszy jest kiepsko zoptymalizowana.
Nie starczyło mi cierpliwości, żeby sprawdzić jak długo wykonuje się to zapytanie.

Jeżeli wykonuję je osobno tzn:

[SQL] pobierz, plaintext 
SELECT id FROM services WHERE name LIKE 'wp%' GROUP BY id
[SQL] pobierz, plaintext 

dostaje 32 rzędy w 0.00 sec

[SQL] pobierz, plaintext 
SELECT count( id ) FROM stats WHERE serviceid
[SQL] pobierz, plaintext 

dostaje odpowiedź w 0.00 sec

jeżeli wezmę oszukam całą procedurę i wstawie do IN wynik zapytania:

[SQL] pobierz, plaintext 
SELECT count( id ) FROM stats WHERE serviceid
IN (60,65,66,67,68,69,70,106,107,108,109,110,111,112,113,114,115,116,117,118,119,120,121,122,123,124,125,126,165,166,167,271)
[SQL] pobierz, plaintext 

dostaję odpoeidź w 0.42 sec

Pytanie - dlaczego wykonanie dwóch selectów na raz trwa nieskończenie dłużej niż takie "oszukanie" zapytanie z IN-em??

Jak można przekonać optymalizera MySQL-owego do poprwnej interpretacji??

[dr_bonzo]

A mozesz dac strukture bazy, z indexami?

[Sh4dow]

z jednej strony prawdopodobnie brakuje indeksów, ale z drugiej prawdopodobnie tworzenie tymczasowej tablicy zamula serwer. pewnie robi jakis zlaczenie tablic zeby pozniej wybrac z nich dane. Chociaz moge sie mylic.

[SongoQ]

Sprawdz tak:

[SQL] pobierz, plaintext 
SELECT count( services.id ) FROM stats, services WHERE stats.serviceid = services.id AND name LIKE 'wp%'
[SQL] pobierz, plaintext 

I teraz tak index na stats.serviceid no i oczywiscie id sa jako PK. Jesli tabele sa jako InnoDB to musisz zrobic relacje. Podaj typ pola name czy to jest varchar czy cos wiekszego. Jesli to jest cos wiekszego to niestety troche trzeba pokombinowac.

Napisz co daje explain dla tego zapytania.

Mozesz tez zamiast services.id dac * ale ine powinno byc roznicy

[mkozak]

z jednej strony prawdopodobnie brakuje indeksów, ale z drugiej prawdopodobnie tworzenie tymczasowej tablicy zamula serwer. pewnie robi jakis zlaczenie tablic zeby pozniej wybrac z nich dane. Chociaz moge sie mylic.

No i to jest ciekawa teoria, bo jeżeli staram się wysterować optymalizera dając SQL_SMALL_RESULT co teoretycznie zmniejsza wielkość tablicy - nie daje żadnego efektu.

Z poprzednimi ripostami iż "brak indexów, albo coś" jakoś się nie zgodzę - przypominam iż osobno zapytania wykonują się bardzo szybko - a na obu tabelach pola id są indexami.

A name jest typu varchar(20)

Ktoś tam prosił o explain

[SQL] pobierz, plaintext 
+----+--------------------+----------+------+---------------+------+---------+------+--------+-------------+
| id | select_type        | TABLE    | type | possible_keys | KEY  | key_len | ref  | rows   | Extra       |
+----+--------------------+----------+------+---------------+------+---------+------+--------+-------------+
|  1 | PRIMARY            | stats    | ALL  | NULL          | NULL | NULL    | NULL | 458920 | USING WHERE | 
|  2 | DEPENDENT SUBQUERY | services | ALL  | NULL          | NULL | NULL    | NULL |    275 | USING WHERE | 
+----+--------------------+----------+------+---------------+------+---------+------+--------+-------------+
[SQL] pobierz, plaintext 

[SQL] pobierz, plaintext 
SELECT count( services.id ) FROM stats, services WHERE stats.serviceid = services.id AND name LIKE 'wp%'
[SQL] pobierz, plaintext 

Natomiast to zapytanie wykonuje sie bardzo optymalnie - 0.17 sec - dzieki - ale jak wyjaśnić tą zagwostkę iż z sub selectem jest niewspółmiernie dłużej niż z podanymi wartosciami.

Ten post edytował mkozak 2.03.2007, 13:05:29

[SongoQ]

O ile pamietam to dla kazdej wartosci bedzie jechal podzapytanie. Jest kilka ksiazek na ten temat z podanymi przykladami kiedy warto stosowac IN a kiedy to jest wrecz zabronione