Optymalizacja zapytania ORDER BY RAND( ), Jak przyśpieszyć to zapytanie - zamula serwer Opcje

[www.aukcje.fm]

Witam,

Mamy na aukcje.fm na dole panel losujący aukcje użytkownika lecz poprzez ORDER BY RAND( ) praca jest powolna i ogólnie obciąża serwer głównie gdy użytkownik ma ponad 100K aukcji i wchodzi bot + ruch ludzi.

W procesie sql to ciągle to samo zapytanie

SELECT *
FROM `tabela1`
WHERE `user` = "200"
AND `closed` =0
AND `suspended` =0
ORDER BY RAND( )

Kod php

[PHP] pobierz, plaintext 
$nazwa;
	$id;
	$cena_kup_teraz;
 
	$question='SELECT * FROM `TABELA1_auctions` WHERE `user`="'.$TPL_user_id.'" AND `closed`=0  ORDER BY RAND() LIMIT 10';
 
	$result = mysql_query($question);
	$i=0;
        if(mysql_num_rows($result)==10){
            while ($row = mysql_fetch_array($result, MYSQL_NUM)) {
                    $idd[$i]=$row[0];
                    $nazwa[$i]= str_replace(array("/","%"), " ",   $row[2] );
                    $photo_uploaded_aukcja =$row[5];
                    $cena_produktu[$i]=cena_produktu($row[10],$row[8]);
                    $i++;
 
            }
        }
	else
	{	
 
 
		$result = mysql_query("SELECT   TABELA1_auctions.id,TABELA1_auctions.title, TABELA1_auctions.minimum_bid ,TABELA1_auctions.buy_now
                    FROM TABELA1_auctions,TABELA1_featured WHERE TABELA1_auctions.id= TABELA1_featured.auction AND closed='0' AND
                    TABELA1_featured.featured='y'
                    ORDER BY RAND() LIMIT 0, 10");
		$i=0;	
		while ($row = mysql_fetch_array($result, MYSQL_NUM)) 			{
                     $idd[$i]=$row[0];
                    $nazwa[$i]=$row[1];
                    $cena_produktu[$i]=cena_produktu($row[3],$row[2]);
                    $i++; 
		}			
	}
 
 
 
[PHP] pobierz, plaintext 

Jak można np. dać losowanie szybsze lub nie z pośród całości 100K rekordów lecz z np 200 rekordów od numeru ID aktualnej aukcji. Lub jest inny sposób?

[emajl22]

http://blog.ksiazek.info/2010/05/04/order-by-rand-to-zlo/

powinno pomóc (IMG:style_emoticons/default/smile.gif)

[www.aukcje.fm]

Dzięki, ale nie za bardzo właśnie. My losujemy z zapytaniem o numer użytkownika i gdy ma on zbyt wiele aukcji ponad 100K to zaczynają się schody, ale musimy wykazać w losowaniu wszystkie jego aukcje (pod seo). Jedyne co nam trzeba to uprościć jakoś sam sposób losowego typowania aby nie był za każdym razem z całości 100K rekordów ale z pewnej tylko zawężonej strefy rekordów. (aby do losowania nie wczytywał całości rekordów dla danego usera lecz grupę zawężoną , tak aby zawsze typował na wszystkich 100K aukcji wszystkie 100K aukcji. Przewidziana nagroda dla twórcy (IMG:style_emoticons/default/smile.gif)

[krowal]

[SQL] pobierz, plaintext 
SELECT aukcje.*, (SELECT MAX(a2.id) FROM aukcje AS a2 WHERE a2.user_id = xx LIMIT 200) AS max_id 
FROM aukcje 
WHERE aukcje.user_id = xx AND aukcje.id <= max_id
ORDER BY RAND()
[SQL] pobierz, plaintext 

Nie jestem pewien czy to zadziała, ale mam nadzieję, że zrozumiesz o co mi chodzi (IMG:style_emoticons/default/winksmiley.jpg)

[www.aukcje.fm]

Właśnie nie za bardzo rozumie bo jestem początkujący w php.

Mozę ktoś się podejmie zmiany tego zapytania aby było znacznie szybsze, zapłacę i mam kilka innych kodów php do poprawy i napisania.

[bim2]

Ja miałem ponad milion rekordów i wtedy zaczęły się schody. Rozwiązałem to bardzo prosto (IMG:style_emoticons/default/smile.gif) Pobrałem ilość danych usera.
Czyli liczysz ile (i tylko ile) aukcji ma user. Z tego przedziału losujesz i wyskakuje ci np liczba 4.
Teraz pobierasz gdzie id = 4

Hmm, teraz widze, że ID nie są po kolei. Najprościej byłoby dopisywać w drugiej kolumne ktora to już aukcja dla usera, żeby szły liczby pokolei. Wtedy będzie naprawde wydajne, a dodatkowa kolumna dużo miejsca nie zabierze.

[kiler129]

Modyfikując sposób kolegi wyżej:
Sprawdzasz, że ma np. 109 aukcji, losujesz z przedziału 1 do 109, dostajesz np. 57. Wydajesz zapytanie: SELECT * FROM `table` WHERE `userId` == '123' LIMIT 57,1;
Zapytanie proste, szybkie i w maire randomowe.

Czasami 10x syzbsze jest wykoanie 2 prostych zapytań niż takiego skomplikwoanego (RAND() - nie podlega query cache).

@bim2 - a co jak aukcja wyleci z bazy? (IMG:style_emoticons/default/smile.gif) No zakładajmy, że wyleci ... można oznaczać jako usuniętą itp... ale w końcu zrobi się za dużo śmiecia.

Ten post edytował kiler129 17.12.2010, 20:02:36

[Rid]

Należałoby także przeczytać Select Count -czytałem że znacznie przyspiesza wertowanie danych na dużych bazach danych.

[www.aukcje.fm]

Ok daliśmy radę, nowe zapytanie bez wywoływania całości, przyśpieszyło dość sporo.

Pozdrawiam i zapraszam do testów

www.aukcje.fm

PS. Jeszcze tylko API do napisania integrujące się w systemie API allegro dla programów zewnętrznych zarządzających aukcjami.

Jak ktoś umie takie cudo to proszę o kontakt!

[kiler129]

Wy się zajmijcie najpierw bezpieczeństwem i ogólnie działaniem serwisu a bajery zostawcie na koniec (IMG:style_emoticons/default/smile.gif)
Z oczywistych względów szczegółów pisać publicznie nie będę - jeśli chcesz wiedzieć coś więcej pisz na gg: 6388911

[krowal]

LOL:

Warning: mysql_connect() [function.mysql-connect]: Too many connections in /data/aukcjefm/aukcje.fm/includes/settings.inc.php on line 34
Warning: mysql_select_db() [function.mysql-select-db]: Too many connections in /data/aukcjefm/aukcje.fm/includes/settings.inc.php on line 37
Warning: mysql_select_db() [function.mysql-select-db]: A link to the server could not be established in /data/aukcjefm/aukcje.fm/includes/settings.inc.php on line 37
Warning: mysql_query() [function.mysql-query]: Too many connections in /data/aukcjefm/aukcje.fm/includes/settings.inc.php on line 40
Warning: mysql_query() [function.mysql-query]: A link to the server could not be established in /data/aukcjefm/aukcje.fm/includes/settings.inc.php on line 40
Warning: session_start() [function.session-start]: Cannot send session cookie - headers already sent by (output started at /data/aukcjefm/aukcje.fm/includes/settings.inc.php:34) in /data/aukcjefm/aukcje.fm/includes/settings.inc.php on line 42
Warning: session_start() [function.session-start]: Cannot send session cache limiter - headers already sent (output started at /data/aukcjefm/aukcje.fm/includes/settings.inc.php:34) in /data/aukcjefm/aukcje.fm/includes/settings.inc.php on line 42
Warning: mysql_query() [function.mysql-query]: Too many connections in /data/aukcjefm/aukcje.fm/includes/https.inc.php on line 7
Warning: mysql_query() [function.mysql-query]: A link to the server could not be established in /data/aukcjefm/aukcje.fm/includes/https.inc.php on line 7

Chyba już się im ktoś wziął za bezpieczeństwo (IMG:style_emoticons/default/winksmiley.jpg)

[vokiel]

P.S. Rand(), dość niedawno o tym pisałem MySQL RAND() – jak pobrać losowe wiersze

[Pilsener]

Też było kilka takich tematów. Moim zdaniem warte uwagi jest:
1. "przycięcie" tabeli, czyli ograniczamy (poprzez where, podzapytanie, warunek w join, dodatkową tabelę, dodatkowe pole tabeli) liczbę rekordów do losowania, ograniczenie także może być losowe (np. raz losujemy parzyste rekordy, innym razem nieparzyste, zyskujemy 50%)
2. buforowanie, nawet jeśli wynik losowania ma się zmieniać co każdy refresh to nie musimy losować ciągle z całej tabeli, tylko z wcześniej pobranej, mniejszej jej części (np. w folderze usera tworzymy plik random_auctions i trzymamy tam 100 losowych ID aukcji z których losujemy 10, plik odświeżamy co X czasu)
3. tworzenie szybkich tabel pomocnicznych (memory), stworzymy sobie tabele rand_user, która zawiera tylko zaindeksowane id_aukcji dla jednego usera, teraz losować już szybciej się nie da
4. zrezygnowanie z "order by rand" na rzecz innych algorytmów, pomysłowość ludzka jest tu wprost nieograniczona, mamy pobrać np. 3 losowe więc tworzymy zapytanie:

[SQL] pobierz, plaintext 
SELECT * FROM tabele WHERE id IN(34,5,789,45,23,56)
[SQL] pobierz, plaintext 

- gdzie liczby w IN są losowane w PHP na podstawie liczby rekordów. Przy odrobinie szczęścia znajdziemy 3 rekordy a jeśli nie to zapytanie ma się wykonać jeszcze raz (aż do skutku) - będzie chyba szybsze niż "order by rand"? Można też dawać rekordom numer kolejny (bez dziur), numerowanie wykonywać w tle co jakiś czas - jest to rozwiązanie szybkie a wylosowanie X numerów da nam z bazy X wyników.

(IMG:style_emoticons/default/guitar.gif) (IMG:style_emoticons/default/dry.gif)

Ten post edytował Pilsener 17.12.2010, 21:46:18

[bim2]

LOL:

Chyba już się im ktoś wziął za bezpieczeństwo (IMG:style_emoticons/default/winksmiley.jpg)

Musiałeś wejść w trakcie kopiowania bazy danych do pliku albo testów. (IMG:style_emoticons/default/smile.gif)

Zająłem się już tym i działa. Powiem tylko, że użyłem MIN(), MAX() i odpowiednio to obrobiłem:) Jest może 10 zapytań, ale bardzo wydajnych.

[kiler129]

Musiałeś wejść w trakcie kopiowania bazy danych do pliku albo testów. (IMG:style_emoticons/default/smile.gif)

Zająłem się już tym i działa. Powiem tylko, że użyłem MIN(), MAX() i odpowiednio to obrobiłem:) Jest może 10 zapytań, ale bardzo wydajnych.

O tym serwisie sporo wiadomo już (IMG:style_emoticons/default/smile.gif)
To całe oprogrmowanie to śmiech na sali i kpina w biały dzień - brak obsługi jakichkolwiek błędów.

Miejmy nadzieje, że może idą po rozum do głowy i kogoś zatrudnią.

Ten post edytował kiler129 18.12.2010, 00:45:27