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

[INOMan]

Zrobiłem parę testów i okazuje się, że w większości przypadków COUNT(*) jest jednak

lepsze niż mysql_num_rows - przepraszam i zwracam honor, to ja byłem w błędzie.

Jednak moje zapytania były na tyle dobrze zoptymalizowane a większość serwisów moich lub klientów stoi

na dedykach/VPS, więc nie stanowiło to problemu, szczególnie, że pierwsze zapytanie było ograniczane przez LIMIT.

Przerobiłem swoją klasę do "pagowania" i uaktualniam serwisy (a trochę ich jest)... jednak ona była tak zrobiona, że wysyłało się do niej po prostu dowolny SQL i o resztę troszczyła się sama, dlatego aby nie wejść

w przerabanie tysięcy SQLi, zrobiłem coś takiego:

$sqlTmp = preg_replace("/^\s*?select.*?\s+from\s+/ims", "SELECT COUNT(*) as qty FROM ", $sql);

gdzie $sqlTmp oczywiście służy do określenia liczby wierszy.

Co prawda to jest użycie regexpa który jest trochę zasobożerny, ale to rozwiązanie tymczasowe,

docelowo będzie zrobione przez strpos/inne szybsze nie regexp funkcje...

[Kukiel]

Cieszymy sie, ze udalo Ci sie przerobic tysiace SQLi..

Jakby ktos szukal czegos gotowego to polecam - https://nospor.pl/pager.html

Intuicyjna klasa pager, latwe i szybkie uzycie.

[korpirkor]

Znalazłem sposób na wybieranie danych z bazy i wyświetlanie ile było wyników przed LIMIT

SELECT SQL_CALC_FOUND_ROWS * FROM `wm_auctions` WHERE `id` > 100 LIMIT 10;
SELECT FOUND_ROWS();

[papieros]

No więc właśnie korpirkor ma rację, do tego o czym mówicie służy SQL_CALC_FOUND_ROWS, to jest właśnie po to i aż dziw, że to rozwiązanie pojawiło się dopiero teraz. Nie testowałem pod względem predkości i wydajności ale stosuje na dość skomplikowanych zapytaniach i sporych tablicach - dziala bez zarzutu.

Edyta:

A tu dowód, że należy stosować SQL_CALC_FOUND_ROWS https://dev.mysql.com/doc/refman/4.1/en/inf...tion_found-rows

[slawek22]

Ok, widzę, że masa jakich dziwnych teorii tutaj się szerzy a potem ludzie narzekają, że 1 prosty skrypcik, parę odwiedzin i ich wywalają z serwera...

Poniżej 10k rows nie ma co się zastanawiać, jedynie sobie krzywdę można zrobić...

SQL_NUM_ROWS ma problem z indeksami...

https://www.mysqlperformanceblog.com/2007/0...alc_found_rows/

No i jeszcze te kombinacje alpejskie, COUNT jest przetestowane i teoretycznie lepiej zoptymalizowane bo się tego najczęściej używa i robi dokładnie to samo. Jakim cudem drugie ma być szybsze?

@General: pobieranie wszystkich wierszy a potem liczenie count-em w PHP... to już totalne herezje. Nie no bez jaj. Jeśli masz count i kwerendę po indeksie to mysql w ogóle nie czyta wierszy... wystarczy, że w pamięci ma indeks i od razu "wie" ile będzie wierszy. Po to są indeksy. Nie dość, że obciążasz dysk, CPU, zajmujesz RAM to jeszcze sieć (jak masz sql na oddzielnej maszynie). Chyba gorzej i bardziej nieoptymalnie tego zrobić się już nie da

Właściwie to tak myślałem, do chwili kiedy nie przeczytałem fragmentu o array_slice Jak będziesz miał 1GB tabelę to na każde wyświetlenie pagincaji strony zużyjesz kilka GB pamięci I obyś miał SQL tam gdzie PHP bo usługodawca będzie wściekły jak 2 UU jednocześnie na stronie mu zapcha sieć...

SELECT SQL_CALC_FOUND_ROWS * FROM `wm_auctions` WHERE `id` > 100 LIMIT 10;

SELECT FOUND_ROWS();

To samo co count, tylko że niepotrzebnie są 2 kwerendy i z indeksami sobie podobno nie radzi.

A jak to zoptymalizować dobrze? Jeden prosty UPDATE

SELECT @inum:=1;

UPDATE tabela SET num=(@inum:=@inum+1) ORDER BY __costam

Utworzyć indeks na num.

COUNT to MAX(num) from tabela

Wiersze to

SELECT * FROM tabela WHERE num >= elementow_na_stronie * strona LIMIT elementow_na_stronie

(czyli nie czyta każdego wiersza tak jak przy limit X,Y, X nie jest problemem bo ogranicza ilość wyników... to Y stanowi problem, bo mysql musi przeczytać Y wierszy zanim wybierze X)

Oczywiście default pola num w tabeli = -1, tak żeby po dodaniu nowych wierszy nam nie psuły paginacji. Potem trzeba zrobić optymalizację I/O... ALTER TABLE i REBUILD po num, tak, żeby kolejne wiersze zajmowały ciągłą przestrzeń na dysku (chyba, że mamy SSD).

No i w tym momencie nam odpada ORDER BY, I/O SEEK i nieoptymalne LIMIT X,Y (które zawsze musi przeczytać X+Y wierszy przed zwróceniem Y w wyniku)

@Papieros:

If you are using SELECT SQL_CALC_FOUND_ROWS, MySQL must calculate how many rows are in the full result set. However, this is faster than running the query again without LIMIT, because the result set need not be sent to the client.

Żaden dowód... raczej wskazówka dla ludzi @General... żeby nie przesyłał pełnego wyniku do klienta (PHP), żeby sobie zrobić count i array_slice

[INOMan]

SELECT * FROM tabela WHERE num >= elementow_na_stronie * strona LIMIT elementow_na_stronie

Fajnie, tylko co w przypadku kiedy dochodzą dodatkowe warunki, np:

SELECT * FROM tabela WHERE jakas_kolumna = jakas_wartosc

Wtedy num jest nieaktualne i pozostaje tylko COUNT().

[slawek22]

Albo budujesz następną tabelę pomocniczą albo dodajesz kilka następnych kolumn z num zdefiniowanym dla poszczególnych warunków. Optymalizacja często oznacza mniejsze możliwości i gorszy kod, nie ma nic za darmo, ale zawsze możesz sobie zrobić zwykły cache i przebudowywać go cron-em, jeśli tabela wejściowa się zmieniła od ostatniego uruchomienia (crona). W ogóle nie powinno się optymalizować niczego... jeśli nie trzeba

Albo jeśli masz np. kilka joinów i chcesz pełny WHERE to się buduje po prostu pomocniczą tabelę w CRON, która zawiera wszystkie kolumny i wstawia indeksy gdzie trzeba, zwykle działa trochę (z 2-5x) szybciej niż z JOIN zależnie od tego ile ich jest i jak są duże tabele.

Co do gotowych klas... to w takim gotowym kodzie nie da się niczego zoptymalizować tak na prawdę a sama optymalizacja jest bez sensu jeśli nie masz z kilkaset tysięcy wierszy bo trzeba to dobrze zrobić.

Przy optymalizacji zawsze zmieniasz SQL, strukturę tabel i zapytanie (no chyba, że robisz zwykły cache). Nie da się napisać zoptymalizowanej klasy ogólnego przeznaczenia która by to robiła (a przy tak powszechnej funkcjonalności ktoś inny na pewno już dawno zrobiłby to lepiej). Zamiana prostszej funkcji na trudniejszą, która robi więcej to też często powtarzane bzdury. Przecież to nie filozofia... jak masz funkcję A która zwraca zwykły COUNT i funkcję B która zwraca count i umożliwia jeszcze przetwarzanie wierszy... i funkcja A działałaby wolniej to przecież na chłopski rozum od razu kod COUNT wylądowałby w koszu i zrobionoby alias. Bo jaki jest sens trzymania kodu dwóch różnych funkcji, jeśli jedna z nich jest szybsza i ma większe możliwości? Po prostu pewnie się ludziom wydaje, że kod będzie szybszy już przez sam fakt, że jest go więcej i gorzej wygląda