Optymalizacja zapytania, 2 kolumny (PHP czy MySQL) Opcje

[markonix]

Posiadam bardzo prostą bazę w postaci ID (INT) i Data UNIX(INT).

[PHP] pobierz, plaintext 
 
$res = mysql_query("SELECT `time` FROM `views` WHERE `aid` = $aid AND `time` > $ago");
while ($row = mysql_fetch_row($res)) {
	$arr[date('Y-m-d', $row[0])] += 1;	
}
[PHP] pobierz, plaintext 

Tabela $arr służy do generowania wykresu.
Niestety wierszy jest sporo (miliony) i skrypt raczej się wywala. Samo zapytanie wg phpmyadmin to kilka setnych sekundy tak więc prawdopodobnie problemy są ze skryptem PHP. Próbowałem przerzucić działanie na bazę:

[SQL] pobierz, plaintext 
SELECT COUNT(*), FROM_UNIXTIME(`time`, '%Y-%m-%d') FROM `views` WHERE `aid` = 123 GROUP BY FROM_UNIXTIME(`time`, '%Y-%m-%d')
[SQL] pobierz, plaintext 

Ale znów sam skrypt w phpmyadmin wykonuje się ponad 10 sekund. Dodając do tego obróbkę w PHP to znów wywołanie skryptu jest na granicy możliwości.

Jakieś sugestie?

Ten post edytował markonix 24.11.2011, 16:03:29

[Ilware]

dodaj index na kolumnie `aid` i spróbuj zmienić zapytanie na :

[SQL] pobierz, plaintext 
SELECT COUNT(*), FROM_UNIXTIME(`time`, '%Y-%m-%d') AS czas FROM `views` WHERE `aid` = 123 GROUP BY czas 
[SQL] pobierz, plaintext 

[lukaskolista]

Jezeli index nie pomoze to proponuje utworzyc jakas tabele/plik w ktorej/ym beda dane tylko i wylacznie do wykresow. Te dane powinny byc w postaci "tylko do odczytu", czyli w takim formacie, ktorego nie trzeba bedzie parsowac ani obrabiac w zaden posob. Taka tabela/plik powinna byc na bierzaco aktualizowana przez jakis skrypt w cronie ktoremu za bardzo nie zalezy na czasie.

Takie rozwiazanie sprawdzi sie jedynie w przypadku, w ktorym nie potrzeba sledzic wykresu na bierzaco i dozwolone sa opoznienia.

Ten post edytował lukaskolista 24.11.2011, 10:31:33

[markonix]

Indeksy były już nałożone - zapomniałem o tym wspomnieć.

lukaskolista pomysł jest w porządku tylko wykresów jest sporo.
Aktualizacja wymagana jest nie na bieżąco ale tak np. co 15 minut.
Puszczając to cronem to ok, nie ma to wpływu na usera ale szkoda mi troszkę serwera.
Jeżeli danych będzie więcej to może to niekorzystnie wpłynąć na całą stronę dlatego chciałbym to zoptymalizować u źródła.

To tylko dwie kolumny INT i myślałem, że nie będzie tak źle.

Ostatecznie zmienię od drugiej strony - utworzę 3cią kolumną "date" (RRRR-MM-DD) i przerzucę odpowiedzialność na funkcje dodającą wiersze.

Ten post edytował markonix 24.11.2011, 16:02:57

[Bags_Bunny]

Pokaż strukturę tabeli, trochę przykładowych danych i definicje indeksów. Jeśli nie masz wiecej niż kilka milionów rekordów, to z odpowiednimi indeksami pierwsze zapytanie powinno być wystaczająco szybkie. Drugie jest marne - może jedynie wykorzystać indeks na aid. Ile rekordów zwraca baza przy pierwszym zapytaniu?

--
W zasadzie po chwili zastowienia stwierdzam, że powinieneś mieć osobną kolumnę z datą, czyli tak, jak napisałeś wcześniej. Pomogło?

Ten post edytował Bags_Bunny 25.11.2011, 00:21:48

[markonix]

[SQL] pobierz, plaintext 
SELECT COUNT(*) AS `sum` , `date`
FROM `views`
WHERE `aid` = 25
GROUP BY `date`
ORDER BY `date` DESC
LIMIT 0 , 14
[SQL] pobierz, plaintext 

~ Minimum 5 sekund. W każdym dniu do policzenia jest około 100 - 200 tysięcy wyświetleń.
Troszkę mało rozwojowe te zapytanie (liczba wzrośnie i będzie znowu kiszka) ale na razie do zaakceptowania.

Co ciekawe indeks nałożony na date pogarsza sytuację o 3 sekundy.

Edit.. Usunięcie indeksu z `aid` skróciło do ~ 1 sekundy.
Chyba czas przegruntować wiedzę o indeksach..

Ten post edytował markonix 25.11.2011, 00:52:12

[Bags_Bunny]

Nic dziwnego. Indeks na jedną kolumnę powoduje pobieranie wielu przypadkowych rekordów = dodatkowe operacje dyskowe. Dodaj jeden indeks na aid i date; spróbuj też bez sortowania, choć nie powinno być ono problemem.

--
Zmieniłbym też * w count na coś zaindeksowanego - chociażby date. Nie wiem czy MySQL jest w stanie to z tą gwiażdką zoptymalizować.

--
Poczytałem i wygląda na to, że count(*) jest jednak ok.

Ten post edytował Bags_Bunny 25.11.2011, 01:08:53

[markonix]

Podwójny indeks działa bardzo dobrze - czas wykonania poniżej 1 sekundy.
Grupowanie wg daty ułatwia także wyselekcjonowanie ostatnich 14dni, w których miały miejsce wejścia.

Sortowanie jest konieczne, aby wybrać ostatnie 14 dni (potem tylko ksort to posortowania od najmniejszej daty).

Ten post edytował markonix 29.11.2011, 14:47:07