Który jest najszybszy? SELECT SQL_CALC_FOUND_ROWS FROM `table` lub SELECT COUNT (*)

Ograniczając liczbę wierszy zwracanych przez zapytanie SQL, zwykle używaną w stronicowaniu, istnieją dwie metody określania łącznej liczby rekordów:

Metoda 1

Uwzględnij SQL_CALC_FOUND_ROWSopcję w oryginale SELECT, a następnie uzyskaj łączną liczbę wierszy, uruchamiając SELECT FOUND_ROWS():

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

Metoda 2

Uruchom zapytanie normalnie, a następnie uzyskaj łączną liczbę wierszy, uruchamiając SELECT COUNT(*)

SELECT * FROM table WHERE id > 100 LIMIT 10;
SELECT COUNT(*) FROM table WHERE id > 100;  

Która metoda jest najlepsza / najszybsza?

To zależy. Zobacz post na blogu MySQL Performance na ten temat: http://www.mysqlperformanceblog.com/2007/08/28/to-sql_calc_found_rows-or-not-to-sql_calc_found_rows/

Krótkie podsumowanie: Piotr mówi, że to zależy od twoich indeksów i innych czynników. Wiele komentarzy do posta wydaje się mówić, że SQL_CALC_FOUND_ROWS jest prawie zawsze wolniejszy - czasem nawet 10 razy wolniejszy - niż uruchomienie dwóch zapytań.

Wybierając „najlepsze” podejście, ważniejszą kwestią niż szybkość może być łatwość utrzymania i poprawność kodu. Jeśli tak, SQL_CALC_FOUND_ROWS jest preferowany, ponieważ potrzebujesz tylko jednego zapytania. Użycie pojedynczego zapytania całkowicie wyklucza możliwość subtelnej różnicy między zapytaniem głównym i licznikiem, co może prowadzić do niedokładnego COUNT.

MySQL zaczął wycofywać swoją SQL_CALC_FOUND_ROWSfunkcjonalność od wersji 8.0.17 i nowszych.

Dlatego zawsze lepiej jest rozważyć wykonanie zapytania za pomocą LIMIT, a następnie drugiego zapytania z COUNT(*)lub bez w LIMITcelu określenia, czy są dodatkowe wiersze.

Z dokumentów :

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.

COUNT (*) podlega pewnym optymalizacjom. SQL_CALC_FOUND_ROWS powoduje wyłączenie niektórych optymalizacji.

Zamiast tego użyj tych zapytań:

SELECT * FROM tbl_name WHERE id > 100 LIMIT 10;
SELECT COUNT(*) WHERE id > 100;

SQL_CALC_FOUND_ROWSZaobserwowano również, że ogólnie ma więcej problemów, jak wyjaśniono w MySQL WL # 12615 :

SQL_CALC_FOUND_ROWS ma wiele problemów. Przede wszystkim jest powolny. Często byłoby taniej uruchomić zapytanie z LIMIT, a następnie osobnym SELECT COUNT ( ) dla tego samego zapytania, ponieważ COUNT ( ) może korzystać z optymalizacji, których nie można wykonać podczas wyszukiwania całego zestawu wyników (np. Sortowanie plików można pominąć dla COUNT (*), podczas gdy przy CALC_FOUND_ROWS musimy wyłączyć niektóre optymalizacje sortowania plików, aby zagwarantować właściwy wynik)

Co ważniejsze, ma bardzo niejasną semantykę w wielu sytuacjach. W szczególności, gdy zapytanie ma wiele bloków zapytania (np. Z UNION), po prostu nie ma możliwości obliczenia liczby „niedoszłych” wierszy w tym samym czasie, w którym powstaje prawidłowe zapytanie. Ponieważ wykonawca iteratora postępuje w kierunku tego rodzaju zapytań, naprawdę trudno jest spróbować zachować tę samą semantykę. Ponadto, jeśli w zapytaniu występuje wiele LIMITÓW (np. Dla tabel pochodnych), niekoniecznie jest jasne, do którego z nich SQL_CALC_FOUND_ROWS powinien się odnosić. Zatem takie nietrywialne zapytania z konieczności otrzymają inną semantykę w programie wykonującym iterator w porównaniu z tym, co miały wcześniej.

Wreszcie, większość przypadków użycia, w których SQL_CALC_FOUND_ROWS wydaje się przydatna, należy po prostu rozwiązać za pomocą innych mechanizmów niż LIMIT / OFFSET. Np. Książka telefoniczna powinna być paginowana literami (zarówno pod względem UX, jak i wykorzystania indeksu), a nie według numeru rekordu. Dyskusje są coraz częściej przewijane w nieskończoność według daty (znowu pozwala na użycie indeksu), a nie według numeru postu. I tak dalej.

Zgodnie z następującym artykułem: https://www.percona.com/blog/2007/08/28/to-sql_calc_found_rows-or-not-to-sql_calc_found_rows/

Jeśli masz INDEX w swojej klauzuli Where (jeśli id ​​jest indeksowany w twoim przypadku), lepiej nie używać SQL_CALC_FOUND_ROWS i zamiast tego używać 2 zapytań, ale jeśli nie masz indeksu tego, co umieściłeś w swojej klauzuli Where (id w twoim przypadku), to użycie SQL_CALC_FOUND_ROWS jest bardziej wydajne.

IMHO, powód, dla którego 2 zapytania

SELECT * FROM count_test WHERE b = 666 ORDER BY c LIMIT 5;
SELECT count(*) FROM count_test WHERE b = 666;

są szybsze niż używanie SQL_CALC_FOUND_ROWS

SELECT SQL_CALC_FOUND_ROWS * FROM count_test WHERE b = 555 ORDER BY c LIMIT 5;

należy traktować jako szczególny przypadek.

W rzeczywistości zależy to od selektywności klauzuli WHERE w porównaniu z selektywnością klauzuli niejawnej odpowiadającej ORDER + LIMIT.

Jak powiedział Arvids w komentarzu ( http://www.mysqlperformanceblog.com/2007/08/28/to-sql_calc_found_rows-or-not-to-sql_calc_found_rows/#comment-1174394 ), fakt, że EXPLAIN używa lub nie, tabela tymczasowa powinna być dobrą podstawą do ustalenia, czy SCFR będzie szybszy, czy nie.

Ale, jak dodałem ( http://www.mysqlperformanceblog.com/2007/08/28/to-sql_calc_found_rows-or-not-to-sql_calc_found_rows/#comment-8166482 ), wynik naprawdę zależy od przypadku. W przypadku konkretnego paginatora możesz dojść do wniosku, że „dla 3 pierwszych stron użyj 2 zapytań; na kolejnych stronach użyj SCFR ”!

Usunięcie niepotrzebnego kodu SQL i wtedy COUNT(*)będzie szybsze niż SQL_CALC_FOUND_ROWS. Przykład:

SELECT Person.Id, Person.Name, Job.Description, Card.Number
FROM Person
JOIN Job ON Job.Id = Person.Job_Id
LEFT JOIN Card ON Card.Person_Id = Person.Id
WHERE Job.Name = 'WEB Developer'
ORDER BY Person.Name

Następnie policz bez zbędnej części:

SELECT COUNT(*)
FROM Person
JOIN Job ON Job.Id = Person.Job_Id
WHERE Job.Name = 'WEB Developer'

Istnieją inne opcje testów porównawczych:

1.) Funkcja okna bezpośrednio zwraca rzeczywisty rozmiar (testowane w MariaDB):

SELECT 
  `mytable`.*,
  COUNT(*) OVER() AS `total_count`
FROM `mytable`
ORDER BY `mycol`
LIMIT 10, 20

2.) Myśląc po wyjęciu z pudełka, przez większość czasu użytkownicy nie muszą znać DOKŁADNEGO rozmiaru stołu, przybliżenie jest często wystarczające.

SELECT `TABLE_ROWS` AS `rows_approx`
FROM `INFORMATION_SCHEMA`.`TABLES`
WHERE `TABLE_SCHEMA` = DATABASE()
  AND `TABLE_TYPE` = "BASE TABLE"
  AND `TABLE_NAME` = ?