Proste zapytanie SQL a długo trwa. Opcje

[pogdan]

Dlaczego poniższe zapytanie idzie w minuty tzn trwa do kilku minut w tabeli tr_wydanie jest 800 recordów.
Jest index na id, i na kontrahent_id wg mnie i bez tych indexów powinno chodzić szybko.

[SQL] pobierz, plaintext 
        SELECT w.id AS w_id
        FROM tr_wydanie w WHERE w.id IN (
               SELECT max(w2.id)
               FROM tr_wydanie w2
               WHERE w2.wydanie>0 AND w2.id IN (
                      SELECT max(id)
                      FROM tr_wydanie
                      WHERE wydanie > 0 GROUP BY kontrahent_id
               )
               GROUP BY w2.kontrahent_id
         )
[SQL] pobierz, plaintext 

tabela wygląda tak

[SQL] pobierz, plaintext 
| id            | int(11)      
| kontrahent_id | int(11)      
| name          | varchar(255) 
| text          | text         
| created_on    | timestamp    
| wplata        | double       
| action        | varchar(64)  
| kiedy         | datetime     
 
[SQL] pobierz, plaintext 

Po początkowym wgraniu dumpa chodzi te zapytanie bardzo szybko. Po jakimś czasie trwa kilka minut i już tak zawsze jest. Silnik MyISAM.

Ten post edytował pogdan 8.10.2013, 15:16:53

[pmir13]

Dla mysql nie ma znaczenia które z poniższych składni zastosujemy:

[SQL] pobierz, plaintext 
ALTER TABLE t ADD INDEX c(c);
ALTER TABLE t ADD KEY c(c);
CREATE INDEX c ON t(c);
[SQL] pobierz, plaintext 

To wszystko dla normalnego indeksu da nam dokładnie ten sam rezultat. Istnieją drobne różnice, ale w naszym przypadku nie mają znaczenia. Jeśli ktoś nie wierzy, proponuję tworzenie i kasowanie indeksu każdą z tych trzech metod i sprawdzanie SHOW INDEXES FROM t;

Indeks na kilku kolumnach, czyli na przykład (kontrahent_id, kiedy) ma w sobie zawartość wszystkich tych kolumn, posortowaną najpierw według kontrahent_id a później dla takich samych kontrahentów według kiedy.
Normalne indeksy, niezależnie od ilości kolumn ( pomijamy fulltext, spatial itp) są typu BTREE, chodzi o to, by przyspieszyć wyszukiwanie rekordów na podobnej zasadzie jak wyszukiwanie binarne przyspiesza na posortowanym zbiorze.
Indeksy wielokolumnowe zawierają w sobie funkcjonalność indeksów dla kolumn z lewej strony, czyli mając taki podwójny nie ma sensu tworzyć następnego indeksu na pojedynczej kolumnie (kontrahent_id).
Nie pomagają natomiast dla kolumn po prawej stronie, czyli indeks na pojedyncza kolumnę (kiedy) wciąż może być potrzebny.

Jeżeli chodzi o zapytanie, które jak się domyślam ma znajdować dla każdego kontrahenta ostatni rekord według daty w kolumnie kiedy, a jeśli takich rekordów jest więcej to według najwyższego id, przy czym brać pod uwagę tylko rekordy spełniające warunek wydanie>0, to proponowałbym przy założeniu, że id jest kluczem primary, mamy indeksy na (kontrahent_id, id) oraz (kiedy, id), a większość rekordów spełnia warunek wydanie>0, takie rozwiązanie:

[SQL] pobierz, plaintext 
SELECT w.id
FROM tr_wydanie w
WHERE id = 
  ( SELECT id
    FROM tr_wydanie wi
    WHERE w.kontrahent_id = wi.kontrahent_id
    AND wydanie >0
    ORDER BY wi.kiedy DESC , wi.id DESC
    LIMIT 1 )
[SQL] pobierz, plaintext 

W tym przypadku indeks (kontrahent_id, id) będzie wykorzystywany do połączenia z correlated subquery, natomiast indeks (kiedy,id) do ORDER BY kiedy DESC, id DESC, otrzymujemy dość czysty explain:

[SQL] pobierz, plaintext 
+----+--------------------+-------+-------+---------------+---------------+---------+------+--------+--------------------------+
| id | select_type        | TABLE | type  | possible_keys | KEY           | key_len | ref  | rows   | Extra                    |
+----+--------------------+-------+-------+---------------+---------------+---------+------+--------+--------------------------+
|  1 | PRIMARY            | w     | INDEX | NULL          | kontrahent_id | 8       | NULL | 100000 | USING WHERE; USING INDEX |
|  2 | DEPENDENT SUBQUERY | wi    | INDEX | kontrahent_id | kiedy         | 12      | NULL |      1 | USING WHERE              |
+----+--------------------+-------+-------+---------------+---------------+---------+------+--------+--------------------------+
[SQL] pobierz, plaintext 

Na testowej bazie z losowymi rekordami 1k kontrahentów, dla każdego po 100 wpisów, czyli w sumie 100000 rekordów zapytanie działa około 2s, co przy tej strukturze danych i warunkach zapytania jest dość dobrym wynikiem.