Porównanie daty, Różnica 1 sekundy zwiększa czas wykonywania Opcje

[snapshot]

Mam takie proste zapytanie, które wykonuje się około 0.015 sekundy:

[SQL] pobierz, plaintext 
SELECT idsong, COUNT(idsong) plays
FROM on_air
WHERE idradio = 5 AND time >= '2011-05-22 02:20:06'
GROUP BY idsong
ORDER BY plays DESC, idsong ASC
LIMIT 20
[SQL] pobierz, plaintext 

Jeżeli zmniejszę czas w warunku o 1 sekundę to zapytanie wykonywać się będzie... 1.5 sekundy. Jakieś pomysły, bo ja odpadam...?

EDIT:
Teraz granica wynosi 2011-05-22 9:22:07-2011-05-22 9:22:08 (baza jest cały czas się rozszerza)

Ten post edytował snapshot 24.05.2011, 18:26:10

[zbig]

Witam !

Po pierwsze sprawdz czy masz zaindeksowane kolumny "idradio", "time", "idsong", "plays".
Proponuje wspolny index dla "idradio" i "time" i pojedynczy dla pozostalych kolumn.

Po drugie sprawdz ile masz rekordow w Tabeli. Jezeli jest to w granicach 100 000 no to nic sie nie dzieje, jazeli jest 1000 000 pomysl czy rzeczywiscie taka ilosc rekordow musisz w tabeli trzymac.

Po trzecie jezeli nie korzystasz z funkcji daty w DB, to zmien typ kolumny time ( dziwna nazwa kolumny - podobnie jak "day" czy "now" ) na biginteger i trzymaj tam timestamp. Na pewno bedzie szybciej szukac

Pozdrawiam

[snapshot]

Mam index na idradio, time, idsong i łączony dla idradio i idsong. Plays jest aliasem z COUNT(idsong).

W tabeli jest około 650 000 rekordów, niestety nie mogę aktualnie jej zmniejszyć.

Z tego co widziałem na testach to int wcale nie jest szybszy przy porównywaniu dat od datetime.

[zbig]

Witam !

Sorry, masz racje, bo nie zauwazylem ze "plays" jest aliasem.
Powiedz mi jedynie o ile poprawi sie wynik, jezeli wyrzucisz linie

Kod

ORDER BY plays DESC, idsong ASC

Pozdrawiam

[snapshot]

Jest różnica, ale głównie rozchodzi się o tą granicę w warunku, która powoduje tak dużą zmianę w wydajności.

[zbig]

No i niestety tak juz bedzie, a to z jenego prostego powodu.
Zmiana granicy warunku zwieksza najwyrazniej w duzym stopniu ilosc znalezionych rekordow ktore wynikalyby z zapytania

Kod

SELECT count(*)
FROM on_air
WHERE idradio = 5 AND time >= '2011-05-22 02:20:06'

Wyobraz sobie ze jest to np. 200.000 rekordow.
Twoj limit 20 jest w tym przypadku tylko limitem wyswietlanych danych, a nie limitem dla zapytania.

A jasniej chodzi o to , ze przy warunku GROUP BY idsong MYSQL analizuje i grupuje w calym zbiorze znalezionych danych ( zalozmy 200.000 ), a nie w danych limitiwanych do 20.
Dodatkowo musi on posortowac caly zbior przez alias , ktory przybiera jakas wartosc dopiero po pogrupowaniu danych.
I dopiero po tych wszystkich happeningach pokazuje pierwsze 20 wynikow.

W samym MYSQL sprawa jest dosc skomplikowana. W tego typu sytuacjach stosuje sie tzw "Faceted search", ale ma on niewiele wspolnego z MYSQL..
Z kolei w jezyku JAVA tego typu sytuacje mozna rozwiazac stosujac streamowanie wynikow i przniesienie dzieki temu czesci operacji obliczeniowych do warstwy logicznej. Pracujemy wtedy w trybie READ-ONLY , fetch-size ustawia sie na INTEGER_MIN_VAL i w ten sposob mozna bez narazania sie na OUT OF MEMORY przeprowadzic interesujaca Cie operacje w warswie logicznej ( oczywiscie tez w granicach zdrowego rozsadku ) .
Php niestety nie umie tego ....

Pomysl o innym rozwiazaniu , szczegolnie ze Twoja baza , jak wspominales wciaz rosnie.
Moze JAVA LUCENE ? ... ( Tylko nie pokus sie na ZEND LUCENE - odradzam )

Albo bazujac tylko na MYSQL jakis Trigger, ktory bedzie budowal Ci na biezaca jakas tabele wynikowa z ktorej pobierzesz dane bez takich perwersji .

Pozdrawiam

Ten post edytował zbig 25.05.2011, 11:54:41

[melkorm]

Jakiego typu jest kolumna time? Możesz też spróbować zmienić na INT i operować na timestamp'ie.

[zbig]

@melkorm

Jakiego typu jest kolumna time? Możesz też spróbować zmienić na INT i operować na timestamp'ie.

@zbig

Po trzecie jezeli nie korzystasz z funkcji daty w DB, to zmien typ kolumny time ( dziwna nazwa kolumny - podobnie jak "day" czy "now" ) na biginteger i trzymaj tam timestamp. Na pewno bedzie szybciej szukac

Pozdrawiam

[melkorm]

ah, sorry, nie spałem dzisiaj i nie chciało mi się czytać