[SQL, PHP] Podział na strony - optymalny sposób

[korpirkor]

Witam

Stoję przed problemem podziału na strony wpisów pobranych z bazy. Przychodzą mi do głowy dwa sposoby:

1. dwa zapytania:

- Na stronie chcemy wyświetlić 30 wpisów, pobieramy więc je (SELECT... LIMIT offset,30)

- Pobieramy calkowitą ilość wpisów (SELECT COUNT(*)...)

- generujemy podział < [1] [2] [3] >

2. jedno zapytanie:

- Na stronie chcemy wyświetlić 30 wpisów, ale pobieramy 31: (SELECT... LIMIT offset,31)

- Jeżeli pobrano 31, wyświetlamy 30 i tworzymy link do następnej strony NASTĘPNA >

- Jeżeli pobrano mniej, nie wyświetlamy linku nawigacji

Która metoda jest Waszym zdaniem bardziej optymalna ? Może jest jeszcze jakaś inna metoda ?

Pozdrawiam !

[Mion]

Są gotowe klasy do paginacji/stronicowania wyników zapytania.

[korpirkor]

Są gotowe klasy do paginacji/stronicowania wyników zapytania.

No to rzeczywiście mi pomogłeś w kwestii OPTYMALIZACJI ;-)

Zastanawia mnie tylko co jest gorsze: pobieranie nadmiarowych danych czy robienie dodatkowych kwerend...

[pawloski]

Może jest jeszcze jakaś inna metoda ?

optymalna pod względem wydajności metoda to nieużywanie w ogóle offsetów

link "następna" prowadzi do np. '?od='.($ostatni_id+1)

i w zapytaniach WHERE id >= n LIMIT 30

w ten sposób wykorzystasz indeks (często primary) na id, zamiast odczytywać offset+limit rekordów

ale mało kto się do tego stosuje, bo wszyscy chcą URL z numerem strony, najczęściej używa się COUNT(*) który w mysql jest bardzo szybki, zwłaszcza gdy nie ma WHERE w zapytaniu

[a.k.]

Zastanawiam się tylko jak przy przykładowo liczbie 15000 rekordów nie używać offsetów. Nigdzie nie jest powiedziane że rekordy muszą mieć kolejne id w bazie.

Co się tyczy paginacji to koniecznie należy sprawdzić liczbę rekordów i podzielić przez liczbę wyświetlanych pozycji na stronę i zaokrąglić w górę. Z uzyskaną liczbą podstron można zrobić wszystko, tym bardziej warto dać użytkownikowi możliwość dotarcia do ostatniej strony z paginacji.

Kończąc radzę zrobić rzeczywiście tak jak sugerował Mion - skorzystać z gotowej klasy. Koła na nowo nikt nie wymyśli a niejednokrotnie uniknie się niepotrzebnych błędów.

[pawloski]

Zastanawiam się tylko jak przy przykładowo liczbie 15000 rekordów nie używać offsetów. Nigdzie nie jest powiedziane że rekordy muszą mieć kolejne id w bazie.

id muszą być monotoniczne (rosnące albo malejące) ale niekoniecznie kolejne

tak samo można robić z każdym innym indeksem np. https://googlepolska.blogspot.com/, linki starsze posty / nowsze posty, indeksem jest data a nie id

złożoność jest logarytmiczna (index seek) a nie liniowa jak przy offsecie więc skaluje się bardzo dobrze

jedyny problem - nie ma takiego pojęcia jak numer strony, są po prostu różne widoki na bazę, pojęcie poprzedni/następny występuje (klucz większy/mniejszy niż pierwszy/ostatni w widoku)

jak potrzebujesz funkcji np. "przejdź na stronę 51" to musisz odczytać 52*limit rekordów i tego nie obejdziesz, ale zmiana strony z 50 na 51 nie musi powodować pełnego skanu, bo wiesz że jeżeli rekord znajduje się na stronie 1-50 (id wpisu <= id ostatniego ze strony 50, czy >= przy odwrotnym sortowaniu), to nie znajduje się na 51

oczywiście jak stron jest np. 500 to różnicy dużej nie widać, jak baza ma 2 mln rekordów podzielonych na 10 tys. stron to różnica w szybkości przy wejściu na ostatnią stronę jest bardzo duża

[Mion]

No to rzeczywiście mi pomogłeś w kwestii OPTYMALIZACJI ;-)

Ale w twoim problemie NIE MA czego optymalizować, bo niezależnie od tego jak prawidłowo obsłużysz podział na stronicowanie całą robotę realizuje klauzula LIMIT od, ile której wystarczy przekazać odpowiednie parametry. Skro nie wiesz jak to zrealizować możesz skorzystać z uprzejmości osób którzy się na tym lepiej znają i gotowych implementacji przez nich udostępnionych.

Za to optymalizować należy bazę pod kątem danego zapytania w którym jest klauzula limit od, ile zwłaszcza przy bardziej rozbudowanych zapytaniach składających się ze złączeń kilku tabel itd itp, a nie tylko offsetach po kluczu głównym w jednej tabeli.

[INOMan]

Nigdy COUNT do takich rzeczy, NIGDY.

Masz w PHP taką funkcje jak mysql_num_rows/mysqli_num_rows - zwraca liczbę rekordów w danym rezultacie, najpierw zwykły SQL bez LIMIT lub z limitem np. do 1000 (żeby więcej rekordów nie analizować) -> mysql_num_rows -> masz liczbę wierszy, a później właściwe zapytanie wynikające z aktualnego numeru strony z odpowiednim LIMIT, np. LIMIT 30,10 - pag numer 4 przy rozmiarze strony = 10.

[pawloski]

pobieranie wszystkiego a następnie używanie num_rows to najgorsze z możliwych rozwiązań, nadmiernie obciążające dysk i ram

przesyłasz między mysql a php całą tabelę tylko po to, żeby sprawdzić jej rozmiar - od tego jest w sql COUNT(*) czy SQL_CALC_FOUND_ROWS

COUNT(*) bez WHERE ani GROUP ma w mysql złożoność O(1) bo ta liczba jest zapisana w nagłówku tabeli, COUNT(*) z indeksowanym WHERE ma O(log n), tak więc SELECT COUNT(*) jest zawsze szybszy niż SELECT * a później ręczne zliczanie

[General_Depet]

Chyba najlepiej jest pobrać całą tablicę i zapisać do zmiennych tablicowych phpa, następnie php-owy count, a to co ma się wyświetlić na danej stronie ograniczyć w samej tablicy (array_slice) - w ten sposób mamy tylko 1 proste zapytanie do bazy.

[Mion]

Nigdy COUNT do takich rzeczy, NIGDY.Masz w PHP taką funkcje jak mysql_num_rows/mysqli_num_rows -

Niestety nie masz pojęcia o tym co piszesz . Jak już napisał przedpiszca zastosowanie tego co proponujesz jest "obieranie wszystkiego a następnie używanie num_rows to najgorsze z możliwych rozwiązań, "!

[INOMan]

Mion:

po 1sze - jak chcesz się dowiedzieć ile masz "pagów" do dyspozycji to musisz tak zrobić, jeżeli nie to masz tylko przycisk "następny pag"

po 2gie - jak się robi zapytanie SQL to się je optymalizuje, mam w jednym sklepie który zrobiłem tabelę z prawie 0,5 mln rekordów do której jest zapytanie SQL dołączające straight i left joinami 8 innych tabel i zapytanie trwa (pomijając mysql qcache) 0,02 sek.

po 3cie przecież napisałem, że nie trzeba po całości, tylko jak masz np. 100 tys. rekordów to pierwsze zapytanie ograniczasz np. to LIMIT 1000

po 4te nie twierdz, że nie wiem o czym pisze, bo to co napisał Pawlowski:

pobieranie wszystkiego a następnie używanie num_rows to najgorsze z możliwych rozwiązań, nadmiernie obciążające dysk i ram

przesyłasz między mysql a php całą tabelę tylko po to, żeby sprawdzić jej rozmia

to największa bzdura jaką przeczytałem od miesięcy... pawlowski... nic nie jest przesyłane między MySQL a PHP do póki nie pobierzesz rekordów przez mysql(i)_fetch_*, a funkcja mysql_num_rows zwraca z MySQL liczbę wierszy a nie wszystkie dane, LOL jaka kompromitacja człowieku.

Co więcej ta liczba wierszy nie wynika ze scanu po całej tabeli i dołączonych przez joina - korzysta z różnych mechanizmów keszujących mysqla, za to funkcja COUNT() MUSI przeskanować cały rezulatat.

W sumie to po co ja Wam pomagam i uczę potencjalną konkurencje za darmo... zresztą przecież Wy (Mion i Pawlowski) na pewno to wiecie lepiej, ja tylko robię sajty od 10 lat dla klientów z Polski i USA.

[pawloski]

to największa bzdura jaką przeczytałem od miesięcy... pawlowski... nic nie jest przesyłane między MySQL a PHP do póki nie pobierzesz rekordów przez mysql(i)_fetch_*, a funkcja mysql_num_rows zwraca z MySQL liczbę wierszy a nie wszystkie dane, LOL jaka kompromitacja człowieku.

bzdury to ty piszesz

mysql_query powoduje przesłanie i zbuforowanie wszystkich danych

twój mysql_num_rows następnie zlicza ile jest wpisów w tym buforze

możesz wprawdzie użyć mysql_unbuffered_query, wtedy rzeczywiście dopiero mysql_fetch_* pobiera dane, ale z oczywistego powodu nie działa mysql_num_rows przy wyłączonym bufowaniu

Co więcej ta liczba wierszy nie wynika ze scanu po całej tabeli

wynika, jak zrobisz SELECT * w mysql_query to skanujesz całą tabelę, przesyłasz ją do php, aby następnie zliczyć ile jest wierszy i odrzucić całą zawartość

za to funkcja COUNT() MUSI przeskanować cały rezulatat

bzdura do kwadratu

zobacz EXPLAIN SELECT COUNT(*)...

musi przeskanować całość tylko wtedy, gdy są nieindeksowane warunki (co uważam za błąd w schemacie tabeli) i nawet jak robi full scan to jest szybszy niż SELECT *... bo nie jeździ bezsensownie po dysku i nie przesyła do php całego zestawu danych

jak sprawdzasz rozmiar pliku na dysku, to też odczytujesz plik bajt po bajcie zwiększając za każdym razem licznik o 1?

[INOMan]

Zrób sobie testy porównawcze, co jest szybsze - COUNT() czy mysql_num_rows.

Wynik jest buforowany ale po stronie serwera MySQL, musisz zrobić mysql(i)_fetch_* żeby cokolwiek przeszło na "stronę" PHP.

Nigdzie też nie napisałem, że pierwsze zapytanie to ma być "SELECT *", nie wiem skąd Ci się to wzięło.

[Mion]

@Zrób sobie testy porównawcze, co jest szybsze - COUNT() czy mysql_num_rows.

No zrób sobie zrób. Funkcja COUNT() jest wewnętrzną funkcją wbudowaną w oprogramowanie serwera MySQL i jako taka będzie zawsze szybsza i wydajniejsza niż pobranie wszystkich rekordów i ich liczenie przez zewnętrzną funkcje PHP.

Skoro tego nie rozumiesz to twój problem, ale nie wprowadzaj innych w błąd