kurtkrut16 июл 2009 в 14:04

Подсчёт общего количества строк выборке в mySQL при использовании LIMIT

5 мин

67K

MySQL *

+43

Комментарии 79

denvo 16 июл 2009 в 14:16

Прошу прощения за «чайницкий» вопрос, но зачем в «двух классически используемых запросах» первый запрос? Разве второй не возвращает искомое количество строк, удовлетворяющих условию?

maxshopen 16 июл 2009 в 14:27

Он нужен для «пагинатора». COUNT(*) вычисляет общее количество строк удовлетворяющих условию (всего страниц, например), а первый достает некоторый кусочек этих данных (выбранная страница, в данном случае 2-я).

denvo 16 июл 2009 в 14:29

Спасибо, понял.

Просто я всегда делал в обратном порядке:
— запрашивал число строк через COUNT(*)
— вычислял число страниц
— проверял ввод пользователя (а то вдруг кто запросит страницу 100, а их всего две :)
— выбирал нужные строки LIMIT'ом

maxshopen 16 июл 2009 в 19:52

Я тоже. Думаю здесь так написано просто для примера

mocksoul 17 июл 2009 в 05:11

ехх, запрашивать надо не страницу, а оффсет =)

chetzof 16 июл 2009 в 14:45

картинок не видно у меня

kurtkrut 16 июл 2009 в 14:48

Она всего одна. На гугловском Picasa Web Albums. Должно работать хорошо.

cuprum 16 июл 2009 в 15:08

У вас расширение картинки указано GIF, а на сервере лежит расширение gif

4orever 16 июл 2009 в 15:50

Наоборот

kurtkrut 16 июл 2009 в 15:52

это я поменял уже

Joka 16 июл 2009 в 14:58

аналогично картинку не вижу

Syrok 16 июл 2009 в 17:49

странно, если открыть картинку в отдельном окне, то она показывается
может так кто увидит yfrog.com/7ddiagrammag

dienow 16 июл 2009 в 14:55

>>Mysql> SELECT SQL_CALC_FOUND_ROWS * FROM table WHERE column > 1 LIMIT 50, 50;
>>Mysql> SELECT FOUND_ROWS();

>>Несмотря на то, что первый запрос вернёт 50 строк, результатом будет «100», т.к. mySQL пришлось просмотреть именно 100 строк.

Если я не ошибаюсь в этом примере SQL_CALC_FOUND_ROWS лишний, так как с ним FOUND_ROWS вернёт всё-таки число строк в таблице удовлетворяющих условию, а не количество просмотренных строк.

kurtkrut 16 июл 2009 в 17:17

да, действительно. пофиксил

pixxxel 16 июл 2009 в 17:20

Да, тут что-то не то

Joka 16 июл 2009 в 14:59

статья интересная, однако большой минус автору использующему даже в примерах select count(*) — помните — использовать в запросах * это как минимум медленно, как максимум плохой тон.

всегда пишите поля которые вам нужно выбрать.

aubt 16 июл 2009 в 15:31

я читал обратное.
не подкрепите свои слова ссылкой?

Joka 16 июл 2009 в 15:53

я не могу отсканить mysql traning course к сожалению

Joka 16 июл 2009 в 15:56

кстати покажите и вы обратное утверждение

aubt 16 июл 2009 в 16:02

я не пытался с Вами спорить.
ниже Вы сами все уже ответили.

да, в топике речь про myisam и звездочка рассматривается только в контексте count().

1999 16 июл 2009 в 16:02

проблема характерна для таблиц типа InnoDB. В них действительно писать COUNT(*) ни в коем случае нельзя из-за низкой скорости выполнения таких запросов.
для таблиц типа MyISAM такие запросы не страшны.

zerkms 16 июл 2009 в 23:54

в innodb проблема не в count(*), а в том, что в метаданных таблицы не хранится информации о числе записей. так что это применимо только к запросу SELECT COUNT(*) FROM `table`;

andi 16 июл 2009 в 15:51

и правда странное утверждение.
как сказано в мане: для таблиц HEAP и MyISAM функция COUNT(*), которая вызывается для одной таблицы и не содержит предложения WHERE, берется непосредственно из табличной информации.

Joka 16 июл 2009 в 15:56

maisonbisson.com/blog/post/11796/easy-mysql-performance-tips/
вот почитайте и сходите в гугл по запросу mysql count(*) perfomance

andi 16 июл 2009 в 16:02

то-есть я должен верить какому-то сомнительному блогу, а не ману? :)

Joka 16 июл 2009 в 16:04

выбор исключительно за вами чему верить

alexxxst 16 июл 2009 в 15:54

для COUNT это не имеет значения, хотя я, обычно пишу COUNT(id), где id — PK

Shtorkin 16 июл 2009 в 16:23

count(*) работает гораздо быстрее, чем count(field)

alexxxst 16 июл 2009 в 16:38

Где про это почитать?

Shtorkin 16 июл 2009 в 16:48

phpclub.ru/detail/article/mysql_optimize тут рассказано, еще где-то в мануале было, там, где про оптимизацию.

Shtorkin 16 июл 2009 в 16:40

А чего минусовать-то? Проверяйте не на бестолковом запросе, типа «select count(*) from table» а на реальном с WHERE — разница в разы.

dewil 17 июл 2009 в 14:49

все верно.
в pgsql тоже так.
если указывать (*) то база сама найдет поле с лучшим для нее индексом для подсчета.
если указывать конкретно поле, то это может стать не лучшим выбором для скорости.
все можно проверить тестами.

spleenjack 16 июл 2009 в 15:57

Читаем внимательно мануал по COUNT.
Звездочка в COUNT и звездочка в списке полей для выборки ничего общего не имеют.

Joka 16 июл 2009 в 15:59

ага, только это относится к майисам, даю 100 в гору что привычка превыше всего и автор пишет и в иннодб так что замедлит запрос. так что мыслить шире надо, а не только применять все к myisam

zerkms 16 июл 2009 в 23:56

это относится только к 1 типу запросов — запросы без WHERE.

mocksoul 17 июл 2009 в 05:23

да что за чушь.

count(*) всегда быстрее чем count(expr). Ибо count(expr) должен применить expr на каждый ряд и вывести то, где не получилось в итоге null. А count(*) возвращает явно число вернувшихся рядов и все. Другими словами:

select count(*) from table where mycol is not null
select count(my_col) from table

абсолютно одинаковые. А значит делать count(id) это равнозначно дописыванию ненужного where id is not NULL в запрос. Оптимизатор это схавает, но не всегда.

в MyISAM у метаифы в таблице вообще явно записано число рядов. Следовательно, простой запрос select count(*) from table можно вообще не исполнять — а просто выплюнуть это число. В innodb не прокатит, т.к. в 1 момнет времени кол-во рядов в таблице может быть разным в разных транзакциях.

— а вы, фантазёры, блин.

zerkms 17 июл 2009 в 08:38

перечитай, пожалуйста, комментарий, на который я отвечал. я даже выделю тебе важную часть:
>> ага, только это относится к майисам, даю 100 в гору что привычка превыше всего и автор пишет и в иннодб так что замедлит запрос.

мой ответ:
>> это относится только к 1 типу запросов — запросы без WHERE.

я говорил только о SELECT COUNT(*) FROM `table` применительно к озвученным storage. ты понимаешь, что я ответил то же самое, что и ты?
habrahabr.ru/blogs/mysql/64655/#comment_1802657

теперь можешь выдыхать.

mocksoul 17 июл 2009 в 13:30

ррр, ну значит цитировать надо с врапом в 72 символа, а то непонятно иногда ;) Пардон.

middle 16 июл 2009 в 15:57

select * from и select count(*) from — это две большие разницы.
Более того, select count(*) from и select count(field) from вернут разные результаты, если в поле field есть стоки с NULL.

Joka 16 июл 2009 в 15:59

так считать то надо с умом, а не тупо любое поле.

middle 16 июл 2009 в 16:09

Мне ли этого не знать…

mocksoul 17 июл 2009 в 05:24

учите матчасть!!!
count(*) — это вовсе не значит «считать любое поле».

ну ппц. на ком инет держится)

Minras 16 июл 2009 в 16:00

count(*) выберет количество найденных записей, не выбирая конкретные значения.
count(id) не только выберет записи, но и будет анализировать id, что дольше.

easterism 17 июл 2009 в 01:05

поэтому я соблюдаю нейтралитет и всегда пишу COUNT(1)

mocksoul 17 июл 2009 в 05:25

если бы оптимизатор мускуля был бы совснем тупой, то он-таки делал бы внутренний nonnull(1) на твою единичку =)) А хз — может и делает.

chetzof 16 июл 2009 в 16:20

Таблица INNODB, тест на рабочем сервере

SELECT SQL_NO_CACHE count( * )
FROM song

(~3,329,071 всего, запрос занял 0.4882 сек.)

SELECT SQL_NO_CACHE count( song_id )
FROM song

(~3,329,071 всего, запрос занял 0.4811 сек.)

разница минимальна, Explain показвает практически одно и то-же.

Joka 16 июл 2009 в 16:23

искренне рад, только вы нагрузите базу эдак на 1.5к запросов в секунду разнообразных и потом выполняйте вот такие подсчеты, вам эти микросекунды в сумму покажутся вечностью

chetzof 16 июл 2009 в 16:33

Я же написал, сервер рабочий, под нагрузкой. Тем более что обычно count кешируется либо на уровне html, либо в memcaсhed с expire.

Shtorkin 16 июл 2009 в 16:37

Повторите тоже самое с уловием в WHERE типа «song_lenght > бла-бла-бла» или что-то типа того.

chetzof 16 июл 2009 в 16:52

SELECT SQL_NO_CACHE count( song_id )
FROM song WHERE song_added = '2009-07-09'
запрос занял 0.0539 сек
результат 100714

SELECT SQL_NO_CACHE count( * )
FROM song WHERE song_added = '2009-07-09'
запрос занял 0.0512 сек.)
результат 100714

song_added естественно index
Explain одинаков

Думаю разница в пределах погрешности, ибо время выполнения запроса каждый раз прыгало на +-40мс

Shtorkin 16 июл 2009 в 16:57

А вы RANGE запросы попробуйте, BETWEEN воткните по song_added ;-)

AlexanderSergeyev 16 июл 2009 в 17:17

В исполком пришла жалоба:
«Напротив моего окна женская баня. Мне все видно и это отвлекает меня и вообще действует на мой моральный облик. Прошу предоставить мне новую квартиру».
Приехала комиссия, смотрят в окно.
— Ну и что? Ничего не видно!
— А вы на шкаф залезьте!
— Ну, залез, — говорит представитель, — все равно не видно!
— Двигайтесь левее…
— Все равно не видно!
— Еще левее!
Тут представитель двигается и падает с края шкафа.
— Вот видите! А я так целый день!

mocksoul 17 июл 2009 в 05:30

а теперь давайте наконец включим голову и подумаем как сделать базе то, что вы от неё хотите.

1) count(*) (если без where и мы юзаем innodb) — то это просто вытащить длину любого ключа. Как правило — primary key. В queryplan будет type=index
2) count(col) заставит смотреть col is not null на каждом ряде. Если col проиндексирована, то результат будет почти такой же как и в 1 пункте, с той лишь разницей что тут тока btree индекс прокатит. Если же индекса нету — то будет *опа.

И ещё очень умно писать SQL_NO_CACHE там где кеша в принципе быть не может =).

dienow 17 июл 2009 в 07:14

Ну вот здесь www.mysqlperformanceblog.com/2007/04/10/count-vs-countcol/ пишут что для myisam count(*) всё же предпочтительней, и, чего я раньше не знал, COUNT(*) при возможности будет использовать подходящий индекс.

body 18 июл 2009 в 08:16

Для count(*) полностью наоборот.

beat 16 июл 2009 в 15:47

субъективность данного теста по вопросом, так как не была использована деректива SQL_NO_CACHE

4orever 16 июл 2009 в 15:51

вы имели в виду объективность?

beat 16 июл 2009 в 15:57

да, опечатка, извините, это наверное знак что пора идти домой :)

WASD42 18 июл 2009 в 11:22

Для того, чтобы ничего не кэшировалось каждую итерацию используются новые параметры. В чём Вы видите здесь потенциальный кэш?

MaxxArts 16 июл 2009 в 16:05

Когда производительность не имеет особого значения и важны удобство и скорость написания кода, то вполне можно использовать FOUND_ROWS() + SQL_CALC_FOUND_ROWS

Я что-то не понял, FOUND_ROWS() и SQL_CALC_FOUND_ROWS удобнее и быстрее чем COUNT() пишутся? Ну допустим. Поднимите, пожалуйста, руки, кто после этого поста сразу станет использовать FOUND_ROWS() + SQL_CALC_FOUND_ROWS.

justhack 16 июл 2009 в 17:40

у меня дежавю, но где-то недавно я уже про это читал, правда, на английском.

kpumuk 16 июл 2009 в 18:36

Думаю, в самом авторитетном источнике.

Aco 16 июл 2009 в 19:34

Для замеров времени выполнения запросов я бы прекомендовал использовать встроенное профилирование запросов в mySQL + можно получить дополнительную информацию о количестве оперативной памяти, проца и тд
dev.mysql.com/doc/refman/5.1/en/show-profiles.html

Aco 16 июл 2009 в 19:41

Как поведет себя SQL_CALC_FOUND_ROWS если в запросе появится JOIN?

zerkms 16 июл 2009 в 23:58

какая разница, он оперирует выборкой.

WASD42 18 июл 2009 в 11:23

Как раз встроенное профилирование и использовалось.

Aco 18 июл 2009 в 18:50

>> $db – это обёртка над PHP-функциями по работе с mySQL. В ней в частности проводится замер времени исполнения запросов к БД.

думаю использовался функционал класса. мне просто интересна была статистика по использованному процессорного времени и размера оперативной памяти.

egorinsk 16 июл 2009 в 22:07

Вообще, я категорически не советую на таблицах с тысячами строк использовать пагинатор на основе LIMIT, он ж будет тупо перебирать все записи.

zerkms 16 июл 2009 в 23:59

это если юзеры будут ходить на последние таблицы.
ps: LIMIT очень медленный, когда размер строки динамический. используйте FIXED ROWS и тогда смещение на произвольную строку будет вычисляться практически мгновенно.

НЛО прилетело и опубликовало эту надпись здесь

zerkms 19 сен 2011 в 20:46

Это актуально для Myisam только, я тогда ошибался :-)

Art1x 17 июл 2009 в 01:18

Ради интереса, в постгресе:

1. Со астериском
EXPLAIN ANALYZE SELECT COUNT(*) FROM test;
Aggregate (cost=173166.50..173166.51 rows=1 width=0) (actual time=22994.037..22994.038 rows=1 loops=1)
-> Seq Scan on log (cost=0.00..164354.60 rows=3524760 width=0) (actual time=9361.992..21308.870 rows=3568867 loops=1)
Total runtime: 23017.941 ms
2. По primary key
EXPLAIN ANALYZE SELECT COUNT(test_id) FROM test;
Aggregate (cost=173166.50..173166.51 rows=1 width=8) (actual time=23320.353..23320.354 rows=1 loops=1)
-> Seq Scan on log (cost=0.00..164354.60 rows=3524760 width=8) (actual time=9604.659..20754.970 rows=3568867 loops=1)
Total runtime: 23320.404 ms
3. По индексу
EXPLAIN ANALYZE SELECT COUNT(test_time) FROM test;
Aggregate (cost=173166.50..173166.51 rows=1 width=4) (actual time=22335.123..22335.124 rows=1 loops=1)
-> Seq Scan on log (cost=0.00..164354.60 rows=3524760 width=4) (actual time=9584.735..20364.312 rows=3568867 loops=1)
Total runtime: 22335.177 ms

никакой разницы :)

mocksoul 17 июл 2009 в 05:34

а помойму почти 0.5% =) Тем не менее, count(*) даже писать проще. А count(col) надо использовать только когда действительно нужно не просто кол-во рядов.

alexius2 17 июл 2009 в 07:07

Ну так в постгресе для вычисления count нужно все записи просканировать, т.к. там агрегатные ф-ии по-другому реализованы. Как ни пиши, все равно будет seq scan (если без WHERE).