Paginacja MySQL bez podwójnego zapytania?

Zastanawiałem się, czy istnieje sposób na uzyskanie liczby wyników z zapytania MySQL, a jednocześnie ograniczenie wyników.

Jak działa paginacja (jak rozumiem), najpierw robię coś takiego

query = SELECT COUNT(*) FROM `table` WHERE `some_condition`

Po otrzymaniu num_rows (zapytanie) mam liczbę wyników. Ale potem, aby faktycznie ograniczyć wyniki, muszę wykonać drugie zapytanie, takie jak:

query2 = SELECT COUNT(*) FROM `table` WHERE `some_condition` LIMIT 0, 10

Moje pytanie: czy mimo wszystko można pobrać całkowitą liczbę wyników, które zostaną podane, ORAZ ograniczyć wyniki zwracane w jednym zapytaniu? Lub w inny skuteczniejszy sposób. Dzięki!

Nie, tyle aplikacji, które chcą podzielić na strony, musi to zrobić. Jest niezawodny i kuloodporny, choć powoduje dwukrotne wykonanie zapytania. Ale możesz buforować licznik przez kilka sekund, a to bardzo pomoże.

Innym sposobem jest użycie SQL_CALC_FOUND_ROWSklauzuli, a następnie wywołanie SELECT FOUND_ROWS(). poza faktem, że musisz FOUND_ROWS()później wykonać wywołanie, jest z tym problem: jest błąd w MySQL, który łaskocze, co wpływa na ORDER BYzapytania, czyniąc je znacznie wolniejszymi na dużych tabelach niż naiwne podejście dwóch zapytań.

Prawie nigdy nie robię dwóch zapytań.

Po prostu zwróć o jeden wiersz więcej niż jest to potrzebne, wyświetl tylko 10 na stronie, a jeśli jest ich więcej niż jest wyświetlonych, wyświetl przycisk „Dalej”.

SELECT x, y, z FROM `table` WHERE `some_condition` LIMIT 0, 11

// iterate through and display 10 rows.

// if there were 11 rows, display a "Next" button.

Twoje zapytanie powinno zostać zwrócone w kolejności od najbardziej trafnych. Są szanse, że większość ludzi nie będzie się przejmować przejściem na stronę 236 z 412.

Kiedy wyszukujesz w Google, a wyników nie ma na pierwszej stronie, prawdopodobnie przejdziesz do strony drugiej, a nie dziewiątej.

Innym podejściem do uniknięcia podwójnych zapytań jest pobranie najpierw wszystkich wierszy dla bieżącej strony za pomocą klauzuli LIMIT, a następnie wykonanie drugiego zapytania COUNT (*), jeśli pobrano maksymalną liczbę wierszy.

W wielu aplikacjach najbardziej prawdopodobnym wynikiem będzie umieszczenie wszystkich wyników na jednej stronie, a podział na strony jest raczej wyjątkiem niż normą. W takich przypadkach pierwsze zapytanie nie zwróci maksymalnej liczby wyników.

Na przykład odpowiedzi na pytania związane z przepełnieniem stosu rzadko przenoszą się na drugą stronę. Komentarze do odpowiedzi rzadko przekraczają limit 5 lub więcej wymagany do wyświetlenia ich wszystkich.

Tak więc w tych aplikacjach możesz po prostu najpierw wykonać zapytanie z LIMITem, a następnie, o ile ten limit nie zostanie osiągnięty, wiesz dokładnie, ile jest wierszy bez potrzeby wykonywania drugiego zapytania COUNT (*) - co powinno obejmują większość sytuacji.

W większości sytuacji zrobienie tego w dwóch oddzielnych zapytaniach jest znacznie szybsze i mniej zasobochłonne niż zrobienie tego w jednym, nawet jeśli wydaje się to sprzeczne z intuicją.

Jeśli używasz SQL_CALC_FOUND_ROWS, to w przypadku dużych tabel sprawia, że ​​twoje zapytanie jest znacznie wolniejsze, znacznie wolniejsze nawet niż wykonywanie dwóch zapytań, pierwszego z wartością COUNT (*), a drugiego z LIMITem. Powodem tego jest to, że SQL_CALC_FOUND_ROWS powoduje, że klauzula LIMIT jest stosowana po pobraniu wierszy zamiast wcześniej, więc pobiera cały wiersz dla wszystkich możliwych wyników przed zastosowaniem ograniczeń. Nie może tego spełnić indeks, ponieważ w rzeczywistości pobiera dane.

Jeśli zastosujesz podejście z dwoma zapytaniami, z których pierwsze pobiera tylko COUNT (*), a nie pobiera rzeczywistych danych, można to osiągnąć znacznie szybciej, ponieważ zwykle może używać indeksów i nie musi pobierać rzeczywistych danych wiersza dla każdy wiersz, na który patrzy. Następnie drugie zapytanie musi tylko spojrzeć na pierwsze wiersze $ offset + $ limit, a następnie zwrócić.

Ten post z bloga dotyczącego wydajności MySQL wyjaśnia to dalej:

http://www.mysqlperformanceblog.com/2007/08/28/to-sql_calc_found_rows-or-not-to-sql_calc_found_rows/

Więcej informacji na temat optymalizacji stronicowania można znaleźć w tym poście i w tym poście .

Moja odpowiedź może być spóźniona, ale możesz pominąć drugie zapytanie (z ograniczeniem) i po prostu przefiltrować informacje przez skrypt zaplecza. Na przykład w PHP możesz zrobić coś takiego:

if($queryResult > 0) {
   $counter = 0;
   foreach($queryResult AS $result) {
       if($counter >= $startAt AND $counter < $numOfRows) {
            //do what you want here
       }
   $counter++;
   }
}

Ale oczywiście, gdy masz do rozważenia tysiące rekordów, bardzo szybko staje się to nieefektywne. Wstępnie obliczona liczba może być dobrym pomysłem.

Oto dobra lektura na ten temat: http://www.percona.com/ppc2009/PPC2009_mysql_pagination.pdf

query = SELECT col, col2, (SELECT COUNT(*) FROM `table`) AS total FROM `table` WHERE `some_condition` LIMIT 0, 10

Dla każdego, kto szuka odpowiedzi w 2020 roku. Zgodnie z dokumentacją MySQL:

„Modyfikator zapytania SQL_CALC_FOUND_ROWS i towarzysząca mu funkcja FOUND_ROWS () są przestarzałe od MySQL 8.0.17 i zostaną usunięte w przyszłej wersji MySQL. W zamian, rozważ wykonanie zapytania z LIMIT, a następnie drugie zapytanie z COUNT (*) i bez LIMIT, aby określić, czy są dodatkowe wiersze. "

Myślę, że to załatwia sprawę.

https://dev.mysql.com/doc/refman/8.0/en/information-functions.html#function_found-rows

Możesz ponownie wykorzystać większość zapytania w podzapytaniu i ustawić je jako identyfikator. Na przykład zapytanie dotyczące filmu, które znajduje filmy zawierające literę „s”, będzie wyglądać tak w mojej witrynie.

SELECT Movie.*, (
    SELECT Count(1) FROM Movie
        INNER JOIN MovieGenre 
        ON MovieGenre.MovieId = Movie.Id AND MovieGenre.GenreId = 11
    WHERE Title LIKE '%s%'
) AS Count FROM Movie 
    INNER JOIN MovieGenre 
    ON MovieGenre.MovieId = Movie.Id AND MovieGenre.GenreId = 11
WHERE Title LIKE '%s%' LIMIT 8;

Zwróć uwagę, że nie jestem ekspertem od baz danych i mam nadzieję, że ktoś będzie mógł to nieco lepiej zoptymalizować. W tej chwili uruchamianie go bezpośrednio z interfejsu wiersza poleceń SQL zajmuje około 0,02 sekundy na moim laptopie.

SELECT * 
FROM table 
WHERE some_condition 
ORDER BY RAND()
LIMIT 0, 10