Forum PHP.pl > Proste zapytanie SQL a długo trwa.

Pomoc - Szukaj - Użytkownicy - Kalendarz

Pełna wersja: Proste zapytanie SQL a długo trwa.

Forum PHP.pl > Forum > Bazy danych > MySQL

pogdan

8.10.2013, 15:15:35

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.

sowiq

8.10.2013, 15:38:11

IMO trochę przekombinowane to zapytanie. Ja bym raczej zrobił 3 oddzielne, bo MySQL lubi się zaplątać przy zagnieżdżonych podzapytaniach.

Co wypluwa Explain?

[SQL] pobierz, plaintext 
EXPLAIN
(tutaj całe Twoje zapytanie)
[SQL] pobierz, plaintext

[edit]
No i pierwsze co to wypadałoby zrobić index:

[SQL] pobierz, plaintext 
ALTER TABLE tr_wydanie ADD INDEX(id, kontrahent_id)
[SQL] pobierz, plaintext

[edit]
Poprawiłem KEY --> INDEX

pogdan

8.10.2013, 15:47:39

[SQL] pobierz, plaintext 
+----+--------------------+------------+-------+---------------+-----------------------+---------+------+------+-----------------------------+
| id | select_type        | TABLE      | type  | possible_keys | KEY                   | key_len | ref  | rows | Extra                       |
+----+--------------------+------------+-------+---------------+-----------------------+---------+------+------+-----------------------------+
|  1 | PRIMARY            | w          | INDEX | NULL          | PRIMARY               | 4       | NULL |  804 | USING WHERE; USING INDEX    |
|  2 | DEPENDENT SUBQUERY | w2         | INDEX | NULL          | wydanie_kontrahent_id | 4       | NULL |    9 | USING WHERE; USING filesort |
|  3 | DEPENDENT SUBQUERY | tr_wydanie | INDEX | NULL          | wydanie_kontrahent_id | 4       | NULL |    9 | USING WHERE; USING filesort |
+----+--------------------+------------+-------+---------------+-----------------------+---------+------+------+-----------------------------+
3 rows IN SET (0.00 sec)
[SQL] pobierz, plaintext

ten index to dodatkowo? czy index wydanie_kontrahent_id usunąć

sowiq

8.10.2013, 15:49:57

Spróbuj dodać indeks na pola id, id_kontrahenta i zobacz czy będzie poprawa. Jeśli nie, to tak jak pisałem - lepiej chyba rozbić to na 3 oddzielne zapytania.

b4rt3kk

8.10.2013, 16:00:22

Możesz mi wyjaśnić jaki sens ma to podzapytanie?

[SQL] pobierz, plaintext 
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
[SQL] pobierz, plaintext

pogdan

8.10.2013, 16:35:15

tak naprawdę to potrzebuję czegoś takiego gdzie 'kiedy' jest polem typu 'datetime' i sensu nabier dopiero jak jest coś wiećej niż id czyli np. jeszcze name możę teraz już nie bezie take masło maślane.

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

i tak naprawdę jest to podzapytanie to zapytania już własciwego ale już tu długo trwa.
natomiast samo

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

trwa dość szybko. poniżej secundy a nawet 0.00sec pokazuje.
Zupełnie nie rozumiem ma już id wybrane to co ma za problem żeby wybrać po indexie primary_key rekordy (już te z name )

Ja na mój gust to się baza danych sypie. i pewnie sowiqa pomoc zastosuję.

b4rt3kk

8.10.2013, 16:46:02

Spytam jeszcze o jedno, co chcesz osiągnąć, tj. jakie rekordy pobrać? Bo że zapytanie jest nieoptymalne to wiadomo i od jego optymalizacji bym właśnie zaczął. Powiedz co chcesz pobrać, a spróbuję pomóc.

Cytat(sowiq @ 8.10.2013, 16:38:11 )

No i pierwsze co to wypadałoby zrobić index:

[SQL] pobierz, plaintext 
ALTER TABLE tr_wydanie ADD KEY(id, kontrahent_id)
[SQL] pobierz, plaintext

Dodanie klucza nie wpłynie na szybkość wyszukiwania... Tutaj należałoby zastosować INDEX.

[SQL] pobierz, plaintext 
CREATE INDEX kontrahent_id ON tr_wydanie;
[SQL] pobierz, plaintext

pogdan

8.10.2013, 16:55:54

sprawa jest rzeczewiscie dziwna bo te zapytanie , robi to samo , i już zasuwa szybko (być może ustanie ta szybkość po jakimś czasie tak przeczuwam)

[SQL] pobierz, plaintext 
        SELECT w.id AS w_id
        FROM tr_wydanie w 
            INNER JOIN        ( SELECT max(w2.id) AS max_id
                   FROM tr_wydanie w2
                   WHERE w2.wydanie>0 AND w2.kiedy IN (
                          SELECT max(kiedy)
                          FROM tr_wydanie
                          WHERE wydanie > 0 GROUP BY kontrahent_id
                   )
                   GROUP BY w2.kontrahent_id
            ) AS w3 ON w.id= w3.max_id
[SQL] pobierz, plaintext

Strange mysql rules

sowiq

8.10.2013, 21:32:38

Cytat(b4rt3kk @ 8.10.2013, 17:46:02 )

Dodanie klucza nie wpłynie na szybkość wyszukiwania... Tutaj należałoby zastosować INDEX.

Oczywiście masz rację. Już poprawiłem w kodzie powyżej. Dzięki za zwrócenie uwagi.

pogdan

8.10.2013, 22:11:43

Dzięki sowiq i b4rt3kk na razie mi to chodzi jako join a nie jako podzapytanie.

Czym się różni index (id, kontrahent_id) od dwóch indexów odzielnie id i kontrahent_id .

pmir13

10.10.2013, 16:06:12

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.

To jest wersja lo-fi głównej zawartości. Aby zobaczyć pełną wersję z większą zawartością, obrazkami i formatowaniem proszę kliknij tutaj.