GoldenLine
Zaloguj się

konto usunięte

Temat: W jaki sposób limitujecie wyniki w stronicowaniu?

Temat założony raczej z ciekawości.



$query1 = mysql_query('SELECT * FROM `tabela` ORDER BY `order` LIMIT x, x;');


To zapytanie oczywiście zwróci mi pewną ilość wyników na podstawie zmiennych limitowych,
ale nie uzyskam po jego wykonaniu informacji, ile jest wszystkich rekordów w tabeli, by móc obliczyć ilość podstron do wygenerowania dla stronicowania.
Możliwości są z reguły dwie, albo podwójne wykonanie zapytania, najpierw COUNT(), który zwróci ilość wszystkich wyników, a później drugie zapytanie z limitami już dla wyświetlania danych.

Sposób drugi, to wybieranie SELECT'em wszystkich rekordów, pobieranie ich ilości poprzez mysql_num_rows() i odpowiednie obcinanie całej tabeli wyników na podstawie zmiennej aktualnej strony, lecz to rozwiązanie przy ogromnej ilości rekordów będzie mega nieoptymalne.

A jak Wy to robicie? Stosujecie jakieś cache'owanie, czy może jeszcze inne metody?
p.s. temat dotyczy czystego kodu PHP, bez dotatkowych modułów typu Paginator, czy frameworków.

edit:
wywołanie w stylu 


SELECT COUNT(....) AS zmienna, tabela.a, tabela.b, tabela.c. .........

się nie liczy , chcę zastosować SELECT * ! ;-)Jakub Świegot edytował(a) ten post dnia 07.09.12 o godzinie 08:53
Link do wypowiedziLink

konto usunięte

Temat: W jaki sposób limitujecie wyniki w stronicowaniu?

Buduję dwa osobne zapytania.

Pierwsze (dla potrzeb mysql_num_rows()) zawiera wszystkie warunki i joiny z drugiego, właściwego pytania ale nie zawiera żadnych danych poza jednym np. id.

Takie rozwiązanie pozwoliło przyspieszyć wykonywanie zapytania o ilość wyników szczególnie dla złożonych zapytań.
Link do wypowiedziLink

konto usunięte

Temat: W jaki sposób limitujecie wyniki w stronicowaniu?

@Robert - Robię dokładnie tak samo, ale chciałem poznać jak robią to inni, być może są lepsze sposoby niż 2 zapytania..
Link do wypowiedziLink

konto usunięte

Temat: W jaki sposób limitujecie wyniki w stronicowaniu?

Jakub Świegot:
@Robert - Robię dokładnie tak samo, ale chciałem poznać jak robią to inni, być może są lepsze sposoby niż 2 zapytania..

Wątpię by ktoś miał na to magiczną metodę która nie ma nic w tyle. Pobranie wszystkich rekordów nie ma sensu. Skoro pobieram 20 to po co mam pobierać 10.000 żeby pokazać tylko 20 ? Liczysz wszystkie a pobierasz wybrane i tyle.
Link do wypowiedziLink

konto usunięte

Temat: W jaki sposób limitujecie wyniki w stronicowaniu?

Jak na razie robię to dwoma zapytaniami - nie znalazłem lepszego sposobu.
Dla "countów" odczytuję tylko klucz główny/klucze główne, reszta (where, having itd.) jest identyczna jak w zapytaniu wyświetlającym wyniki.

Cacheowanie to zupełnie inna sprawa :)
Link do wypowiedziLink

konto usunięte

Temat: W jaki sposób limitujecie wyniki w stronicowaniu?

Jeżeli chodzi o MySQL to on ma tam taką magiczną stałą/funkcję która się zwie: SQL_CALC_FOUND_ROWS

a tutaj wyjaśnienie dlaczego jej nie stosować: http://mariusz.turek.salon404.pl/post/4,mysql-sql-calc...

a w postgresql nie znam czegoś takiego.
Link do wypowiedziLink
Tomasz Zadora

Tomasz Zadora programuję

Temat: W jaki sposób limitujecie wyniki w stronicowaniu?

Jakub Świegot:
[...]
Sposób drugi, to wybieranie SELECT'em wszystkich rekordów, pobieranie ich ilości poprzez mysql_num_rows() i odpowiednie

W ten sposób musisz pobrać cały rezultat z serwera DB do PHP. Count jest o wiele lepszy bo obliczenie ilości wierszy odbywa się po stronie DB.

PS. rozwalił mnie komentarz "Waldka" pod artykułem który podał Przemek:
waldek 03.02.2011
40
Ludzie zaczeli kopiować wyniki tego tesu i piszą że SQL_CALC_FOUND_ROWS jest zawsze
wolniejsze 40 razy :)
To zależy tez od WHERE - ile rekordow obejmuje. Do pół miliona nie powinno być roznicy.

ROTFL

Sam autor także trochę dowalił - po co ORDER BY w zapytaniach obliczających liczbę wierszy?!Tomasz Zadora edytował(a) ten post dnia 07.09.12 o godzinie 11:40
Link do wypowiedziLink
Daniel Kos

Daniel Kos Prezes, Digital
Software

Temat: W jaki sposób limitujecie wyniki w stronicowaniu?

Najlepiej nie zliczać rekordów. Po prostu :) Z reguły użytkownik jest zainteresowany tylko pierwszymi stronami i raczej naciska next page a nie pokaż wyniki dla strony 2034.
Oczywiście nie zawsze tak się da i chcemy mieć dokładną ilość rekordów. Niestety zliczanie zawsze będzie wolne, bo baza musi "przeorać" wszystkie krotki spełniające dany warunek. Można ewentualnie zliczać liczbę wierszy tylko raz, przy pokazaniu pierwszej strony. Naturalnie jak użytkownik będzie przełączał się na kolejne strony a w międzyczasie dojdzie kolejny wiersz to nasza wartość będzie nieaktualna, ale być może jest to do zaakceptowania - w końcu wynik jest "prawie dokładny"
Link do wypowiedziLink
Tomasz Zadora

Tomasz Zadora programuję

Temat: W jaki sposób limitujecie wyniki w stronicowaniu?

Daniel Kos:
[...]
ilość rekordów. Niestety zliczanie zawsze będzie wolne, bo baza musi "przeorać" wszystkie krotki spełniające dany warunek.

Właśnie, że nie zawsze. Jeżeli korzystamy z silnika MyISAM (jeżeli mówimy o MySQL) to MySQL stara się skorzystać z indeksów, a w przypadku prostych zapytań, bez WHERE typu: SELECT COUNT(*) as `numrows` FROM `table` nawet nie korzysta z indeksów tylko wew. licznika liczby wierszy (metadata).

Przy transakcyjnych silnikach (InnoDB) sprawa już wygląda inaczej.

http://www.mysqlperformanceblog.com/2006/12/01/count-f...Tomasz Zadora edytował(a) ten post dnia 07.09.12 o godzinie 12:08
Link do wypowiedziLink

konto usunięte

Temat: W jaki sposób limitujecie wyniki w stronicowaniu?

Tomasz Zadora:
.. po co ORDER BY w zapytaniach obliczających liczbę wierszy?!

Dokładnie.
Link do wypowiedziLink
Daniel Kos

Daniel Kos Prezes, Digital
Software

Temat: W jaki sposób limitujecie wyniki w stronicowaniu?

Tomasz Zadora:
Właśnie, że nie zawsze. Jeżeli korzystamy z silnika MyISAM (jeżeli mówimy o MySQL) to MySQL stara się skorzystać z indeksów, a w przypadku prostych zapytań, bez WHERE typu: SELECT COUNT(*) as `numrows` FROM `table` nawet nie korzysta z indeksów tylko wew. licznika liczby wierszy (metadata).
Moja uwaga miała charakter ogólny. Czy z użyciem indeksów czy nie baza musi sprawdzić wszystkie rekordy danej tabeli. W przypadku gdy reguły filtrowania pokrywają indeksy baza skorzysta tylko z nich ale i tak jest to zawsze wolniejsze w porównaniu z takim samym zapytaniem ale z klauzulą limit/offset. A ostatni przypadek, który opisałeś - owszem jest natychmiastowy ale rzeczywiste zapytania w 99% przypadków są bardziej skomplikowane ;)
Link do wypowiedziLink
Tomasz Zadora

Tomasz Zadora programuję

Temat: W jaki sposób limitujecie wyniki w stronicowaniu?

Daniel Kos:

Moja uwaga miała charakter ogólny.

Sorry ale nie... w informatyce różnica jednego znaku to czasami ogromna różnica. Tak samo OGROMNĄ różnicą jest to czy zapytanie przejdzie po całym indeksie czy całej tabeli (index scan vs table scan).
Czy z użyciem indeksów czy nie baza musi sprawdzić wszystkie rekordy danej tabeli.

Jeszcze raz: użycie indeksu przy count() NIE OZNACZA przejścia przez wszystkie rekordy tabeli/sprawdzenie wszystkich rekordów tabeli. To nie jest to samo.Tomasz Zadora edytował(a) ten post dnia 07.09.12 o godzinie 13:21
Link do wypowiedziLink
Tomasz Zadora

Tomasz Zadora programuję

Temat: W jaki sposób limitujecie wyniki w stronicowaniu?

Jeszcze odnośnie sposobu dla Jakuba (autora tego wątku): w niektórych przypadkach warto zastosować licznik.

Np. masz tabele na kategorie i produkty.

I teraz masz w sklepie listing produktów z danej kategorii.

Aby nie robić count, dodajesz licznik produktów w danej kategorii. I teraz przy insert/update/delete produktu aktualizujesz licznik produktów w tabeli kategorii. Oczywiscie przy update tylko jezeli kategoria sie zmienia (wtedy 2 aktualizacje licznika).

W ten sposób zamiast robić count po prostu pobierasz liczbę produktów z kategorii.

Nie da się oczywiście załatwić wszystkich sytuacji w ten sposób ale jest to bardzo przydatny trik
Link do wypowiedziLink
Daniel Kos

Daniel Kos Prezes, Digital
Software

Temat: W jaki sposób limitujecie wyniki w stronicowaniu?

Sorry ale nie... w informatyce różnica jednego znaku to czasami ogromna różnica. Tak samo OGROMNĄ różnicą jest to czy zapytanie przejdzie po całym indeksie czy całej tabeli (index scan vs table scan).
Czy z użyciem indeksów czy nie baza musi sprawdzić wszystkie rekordy danej tabeli.
Jeszcze raz: użycie indeksu przy count() NIE OZNACZA przejścia przez wszystkie rekordy tabeli/sprawdzenie wszystkich rekordów tabeli. To nie jest to samo.

Niepotrzebnie się czepiasz. Może źle się wyraziłem. Chodziło mi o to że jak masz indeks to szukasz wszystkich pasujących wierszy w indeksie, jak nie masz to posiłkujesz się skanowaniem tabeli - ale w w przypadku LIMIT X zatrzymujesz się po X szukaniach (przeważnie dużo mniej - zależy od typu indeksu, jego organizacji i konkretnej implementacji), a dla count(*) szukasz aż znajdziesz wszystko (co musi trwać dłużej).
Link do wypowiedziLink

konto usunięte

Temat: W jaki sposób limitujecie wyniki w stronicowaniu?

rozwiązanie mało programistyczne, bardziej bazodanowe

wynik zapytania do tabeli tymczasowej

count na tabeli tymczasowej do zmiennej

select tabela_tymczasowa.*, @zmienna as ile_rek
from tabela_tymczasowa

procesujemy raz, jak spodziewany wynik jest mały to do tabeli MEMORY
przy założeniu że zapytanie jest skomplikowane to może być to opłacalne czasowo
Link do wypowiedziLink

konto usunięte

Temat: W jaki sposób limitujecie wyniki w stronicowaniu?

Tomasz Zadora:
Jeszcze odnośnie sposobu dla Jakuba (autora tego wątku): w niektórych przypadkach warto zastosować licznik.

Np. masz tabele na kategorie i produkty.

I teraz masz w sklepie listing produktów z danej kategorii.

Aby nie robić count, dodajesz licznik produktów w danej kategorii. I teraz przy insert/update/delete produktu aktualizujesz licznik produktów w tabeli kategorii. Oczywiscie przy update tylko jezeli kategoria sie zmienia (wtedy 2 aktualizacje licznika).

W ten sposób zamiast robić count po prostu pobierasz liczbę produktów z kategorii.

Nie da się oczywiście załatwić wszystkich sytuacji w ten sposób ale jest to bardzo przydatny trik

Ciekawy pomysł, ale pobranie wartości licznika to kolejne zapytanie (jeśli liczniki są w innej tabeli), a dodanie pola licznika do tabeli z produktami/items'ami to trochę bez sensu powielanie danych..
Link do wypowiedziLink
Tomasz Zadora

Tomasz Zadora programuję

Temat: W jaki sposób limitujecie wyniki w stronicowaniu?

Jakub Świegot:
Ciekawy pomysł, ale pobranie wartości licznika to kolejne zapytanie (jeśli liczniki są w innej tabeli), a dodanie pola

Tylko, że to jest proste zapytanie po kluczu podstawowym, o wiele szybsze niż count() czy mysql_num_rows.
licznika do tabeli z produktami/items'ami to trochę bez sensu powielanie danych..

Tak to straszne... dodatkowa kolumna liczbowa, kilka dodatkowych bajtów na wiersz.

Jeżeli już to najtrudniejsze jest tutaj zachowanie spójności danych, bo trzeba napisać odpowiednią procedurę po stronie bazy obsługującą licznik albo odpowiednio zrobić to w warstwie aplikacji.

Tak czy inaczej jednym zapytaniem nie załatwisz stronnicowania.Tomasz Zadora edytował(a) ten post dnia 07.09.12 o godzinie 15:19
Link do wypowiedziLink

konto usunięte

Temat: W jaki sposób limitujecie wyniki w stronicowaniu?

Tomasz Zadora:
Tak czy inaczej jednym zapytaniem nie załatwisz stronnicowania.

Właśnie, że załatwisz, tylko w ch**** sposób



$query = mysql_query('SELECT * FROM `tabela` ORDER BY `field_id` ASC;');

$data = array();

while($result = mysql_fetch_array($query)) $data[] = $result;



$records_found = count($data);  /* $records_found = mysql_num_rows($query); */



$limit_low = ($current_page * 10) - 10;

$limit_high = 10;

$data = array_slice($data, $limit_low, $limit_high);



$smarty->assign('data', $data); /* np. */

$smarty->assign('records', $records_found);

$smarty->assign('pages', ceil($records_found/10));
Jakub Świegot edytował(a) ten post dnia 07.09.12 o godzinie 17:32
Link do wypowiedziLink

konto usunięte

Temat: W jaki sposób limitujecie wyniki w stronicowaniu?

Jakub Świegot:
A jak Wy to robicie? Stosujecie jakieś cache'owanie, czy może jeszcze inne metody?




setItemCountPerPage(10)

+ oczywiście Zend_Paginator_Adapter_DbTableSelect

:D
Link do wypowiedziLink

konto usunięte

Temat: W jaki sposób limitujecie wyniki w stronicowaniu?

od jakiegoś czasu stosuję nieco inną metodę.
doszedłem do wniosku że ludzie zwykle chcą wiedzieć, że jest następna strona, a nie że jest 10 kolejnych i rzadko kiedy skaczą X stron do przodu (oczywiście wszystko zależy czego strona dotyczy)
potrzebując zatem X rekordów wyciągam X+1. jak dostanę X+1 to znaczy że w bazie jest więcej niż X, ostatni rekord pomijam, wyświetlam na stronie stronicowanie w dół + przycisk "następna"
(a jak jest X lub mniej to znaczy, że nie ma następnej strony).
a tam gdzie jednak muszę znać ilość rekordów to upraszczam zapytanie by trwało szybciej.

proponuję zrobić sobie statystyki, które przyciski paginatora ludzie używają.Krzysztof D. edytował(a) ten post dnia 07.09.12 o godzinie 18:42
Link do wypowiedziLink
« Wróć do tematów
Odpowiedz

Podobne tematy





Wyślij zaproszenie do
Anuluj