Problem z prędkością wykonywania zapytania, Przepisanie wartości jednej kolumny do drugiej Opcje

[Radek_1]

Witam,

mam problem z prędkością wykonywania zapytania.
Cron uruchamia skrypt codziennie i jeżeli zajdą pewne warunki to przepisuje wartości z jednej kolumny do drugiej. W tabeli jest póki co ok. 37 tyś rekordów. Problem w tym, że przepisywanie trwa bardzo długo, tj. tylko 2tyś rekordów na minutę. Wydawało mi się, że taka nieskomplikowana rzecz powinna trwać parę sekund.
Na początku myślałem, że to wina pythona, dlatego przepisałem to na php, ale wciąż to samo.

Sam kod do przepisywania wygląda tak:

[PHP] pobierz, plaintext 
		$sql="SELECT current_month, name FROM time_online WHERE current_month>0";
		$query = mysql_db_query($db_database, $sql);
		while ($high = mysql_fetch_array($query)) {
			mysql_query("UPDATE time_online SET last_month='".$high[0]."', current_month='0' WHERE name='".$high[1]."'");
		}
[PHP] pobierz, plaintext 

Całość wykonuje się na serwerach home.pl, więc nie powinno być dużych opóźnień. Co o tym sądzicie? Da się to jakoś przyspieszyć, czy taka "prędkość" to norma?


Edit:
Zmieniłem warunek po którym edytujemy wpisy z name na uid i... idzie o niebo szybciej. Taki głupi błąd :/

Ten post edytował Radek_1 1.09.2011, 20:32:16

[6nom]

W ten sposob skrypt zawsze bedzie dzialal wolno.

Zobacz co robisz:
1. Pobierasz w pesymistycznym przypadku 37 tys. rekordow
2. Iterujesz po rekordach i wykonujesz X zapytan update do bazy (kazde jest kosztowne ze wzgledu na uzyte mechanizmy blokowania w zaleznosci od silnika)

Lepiej bys zrobil, jakbys wykonal jedno zapytanie update do bazy, bez zadnego pobierania rekordow i iterowania po nich. Poszukaj na googlach informacji na temat update from select

P.S.
Przesiadlem sie na postgresa i nie wiem, czy MySQL obsluguje juz mozliwosc uzycia tej samej tabeli w subquery update'a, ale na to tez sa sposoby

[maly_swd]

A takie cos nie dziala?
Bez tych selectow i petli... dajesz tylko takie cos ...

Druga sprawa czy masz indexy?
Jesli nie to daj chociaz na current_month

[SQL] pobierz, plaintext 
UPDATE time_online SET last_month=current_month, current_month=0 WHERE current_month>0
[SQL] pobierz, plaintext 

[Radek_1]

Dziękuję bardzo za pomoc, dużo szybciej chodzi. W zasadzie sam powinienem na to wpaść, taki banał się teraz to wydaje, jak napisaliście swoje rozwiązania... (IMG:style_emoticons/default/wstydnis.gif)


To kolejne pytanko, o sam skrypt. Skrypt jest w pythonie, przerobiłem go, aby wstawić na forum na php, ale sądzę, że to jest sprawą drugorzędną.
Pobiera dane ze strony, następnie sprawdza czy użytkownik (zmienna $name_p) istnieje.
Jeżeli nie dodaje go do bazy danych.
Jeżeli istnieje to dodaje do jego czasu w aktualnym dniu i miesiącu 15 minut.

[PHP] pobierz, plaintext 
 
$sql="SELECT COUNT(1), UID FROM time_online WHERE name='".$name_p."'";
$query = mysql_db_query($db_database, $sql);
$row = mysql_fetch_array($query);
if($row[0]==1)
	mysql_query("UPDATE time_online SET ".$dzien_tyg."=".$dzien_tyg."+15, current_month=current_month+15, online='1' WHERE UID='".$row[1]."'");
else
	mysql_query("INSERT INTO time_online SET name='".$name_p."', current_month='15', online='1', ".$dzien_tyg."='15'");
[PHP] pobierz, plaintext 

Pytanie, czy da się to jakoś przyspieszyć? Docelowo ma być ponad pół mln rekordów i chciałbym, żeby to wykonywało się jak najszybciej.
Wąskim gardłem wg. mnie jest tutaj zapytanko pobierające danego użytkownika.

Inne rozwiązanie:

[PHP] pobierz, plaintext 
 
$wynik=mysql_query("UPDATE time_online SET ".$dzien_tyg."=".$dzien_tyg."+15, current_month=current_month+15, online='1' WHERE name='".$name_p."'");
if($wynik==0)
	mysql_query("INSERT INTO time_online SET name='".$name_p."', current_month='15', online='1', ".$dzien_tyg."='15'")
[PHP] pobierz, plaintext 

Takie coś nie ma sensu, bo mysql_query zwraca 1 jeżeli wykona się zapytanie, albo 0 jeżeli nie. Jeżeli użytkownik nie istnieje to i tak zwróci 1, bo pytanie się wykona, ale nie zaktualizuje żadnego rekordu, bo rekord o podanych warunkach nie istnieje. Jakieś sugestie?

Edit:
Znalazłem funkcje mysql_affected_rows() ale czy ona działa szybciej niż sposób przedstawiony na początku postu - śmiem wątpić.

Ten post edytował Radek_1 2.09.2011, 22:48:05

[mortus]

Chyba niepotrzebnie w tabeli time_online zapisujesz nazwy użytkowników, bo masz tam już ich id (kolumna UID). Jeśli jednak tak ma być, to nałóż indeks zarówno na UID, jak i na name (zauważ, że wartości w tych kolumnach powinny być unikalne).
Do sprawdzenia ilości rekordów pobranych przez zapytanie SQL (SELECT lub SHOW) możesz użyć mysql_num_rows(), jednak funkcja ta jest wolniejsza aniżeli wykonanie zapytania zliczającego i wyekstrahowanie liczby z wyniku tego zapytania.
mysql_affected_rows() pobiera liczbę rekordów przetworzonych (INSERT, UPDATE, REPLACE lub DELETE) przez zapytanie SQL i jest to jedyny sposób, aby liczbę tych przetworzonych rekordów pobrać.
W sumie to pierwsze zapytanie możesz pominąć:

[PHP] pobierz, plaintext 
$result = mysql_query("UPDATE time_online SET ".$dzien_tyg."=".$dzien_tyg."+15, current_month=current_month+15, online='1' WHERE name='".$name_p."'");
if(mysql_affected_rows($result) == 0) {
 mysql_query("INSERT INTO time_online SET name='".$name_p."', current_month='15', online='1', ".$dzien_tyg."='15'");
}
[PHP] pobierz, plaintext 

Całość wygląda na obsługę statusu online użytkownika, ale nie wiem, po co ten INSERT.

[Radek_1]

mortus, zgodnie z tym co napisałem po edytowaniu postu, funkcję mysql_affected_rows znalazłem, jednak jest ona wolniejsza niż sprawdzanie innym zapytaniem czy użytkownik istnieje (sprawdziłem).

Skrypt wyszukuje wszystkich użytkowników na stronie i zlicza ich czas bycia online. Jeżeli użytkownik jest nowy, to dodaje go do czasu "obserwowanych", stąd ten INSERT.

[mortus]

Nie zrozumiałeś mnie, mysql_affected_rows() używamy, aby uzyskać liczbę rekordów zmienionych przez zapytanie z INSERT, UPDATE, REPLACE i DELETE. Inaczej nie możemy tej liczby uzyskać, bo powyższe zapytania nie zwracają żadnych rekordów.
Natomiast mysql_num_rows() możemy użyć, jeśli chcemy uzyskać liczbę rekordów pobranych za pomocą SELECT. Funkcja ta jest jednak wolna.

[PHP] pobierz, plaintext 
// wariant szybszy$sql = "SELECT COUNT(*) AS `rows` FROM `table`";
$result = mysql_query($sql);
$row = mysql_fetch_assoc($result)
$rowsNumber = $row['rows'];
// wariant wolniejszy
$sql = "SELECT * FROM `table`";
$result = mysql_query($sql);
$rowsNumber = mysql_num_rows($result);
 
// natomiast mysql_affected_rows() używamy dla zapytań z INSERT, UPDATE, REPLACE lub DELETE
// zapytanie aktualizuje czas przebywania online użytkownika o nazwie $name_p
$sql = "UPDATE time_online SET ".$dzien_tyg."=".$dzien_tyg."+15, current_month=current_month+15, online='1' WHERE name='".$name_p."'";
$result = mysql_query($sql);
// sprawdzamy, czy jakieś rekordy zostały zaktualizowane i jeśli nie został zaktualizowany żaden rekord, to poniższy warunek jest spełniony
if(mysql_affected_rows($result) == 0) {
 // co oznacza, że użytkownik o nazwie $name_p jeszcze w naszej tabeli nie istnieje i musimy go dodać
 mysql_query("INSERT INTO time_online SET name='".$name_p."', current_month='15', online='1', ".$dzien_tyg."='15'");
}
[PHP] pobierz, plaintext 

Akurat Twój skrypt to szczególny przypadek, w którym - jak widzisz - nie ma potrzeby sprawdzania za pomocą SELECT, czy użytkownik w tabeli time_online istnieje, wystarczy bowiem spróbować dokonać aktualizacji rekordu, a jeśli aktualizacja się nie powiedzie (mysql_affected_rows() zwróci 0) to będzie to oznaczać, że użytkownika o nazwie $name_p nie ma w tabeli time_online i trzeba go dodać.
Odsyłam do manuala: mysql_num_rows(), mysql_affected_rows().

Skrypt wyszukuje wszystkich użytkowników na stronie i zlicza ich czas bycia online. Jeżeli użytkownik jest nowy, to dodaje go do czasu "obserwowanych", stąd ten INSERT.

Nie zmienia to faktu, że lepiej do wyszukiwania użytkownika nadawałaby się kolumna UID aniżeli name, oraz że obie te kolumny powinny być unikalne i śmiało można nałożyć na nie indeksy, co przyspieszy nieco działanie skryptu.

Ten post edytował mortus 4.09.2011, 09:24:59

[Radek_1]

mortus, doskonale rozumiem co napisałeś wcześniej. Powtórzę jeszcze raz, sprawdzanie czy nastąpiła aktualizacja rekordu przy pomocy mysql_affected_rows() jest _wolniejsze_ niż sprawdzenie czy rekord istnieje i późniejsza ewentualna aktualizacja/wstawienie.
Tj. szybciej jest:
1. Sprawdzić czy użytkownik istnieje
2. Zaktualizować jeżeli istnieje
3. Wstawić nowy rekord jeżeli nie istnieje

Niż:
1. Zaktualizować rekord
2. Sprawdzić mysql_affected_rows() czy nastąpiła aktualizacja (w przypadku gdy użytkownik nie istnieje to go nie zaktualizuje, no bo jak?)
3. Wstawić nowy rekord jeżeli nie nastąpiła aktualizacja.

Nie zmienia to faktu, że lepiej do wyszukiwania użytkownika nadawałaby się kolumna UID aniżeli name, oraz że obie te kolumny powinny być unikalne i śmiało można nałożyć na nie indeksy, co przyspieszy nieco działanie skryptu.

W jaki sposób wyszukiwać po UID? Skoro strona na której sprawdzam czy ktoś jest online jest niezależna ode mnie i napisane są na niej tylko i wyłącznie nazwy? A co do name to nadałem index i jest unikalna (IMG:style_emoticons/default/smile.gif)

Ten post edytował Radek_1 4.09.2011, 18:30:15