разбивка по страницам

zerkms · 13 Дек 2010

Какие выводы, что SQL_CALC_FOUND_ROWS сосёт? Ну это и было сказано в самом начале ведь, не?

0.73 + 0.01 (2 запроса) < 1.12

м?

Активист · 13 Дек 2010

Хм, как оказалось - не всегда, вот еще интересней результат, странно....

Код:

mysql> SELECT SQL_NO_CACHE SQL_CALC_FOUND_ROWS * FROM count_test WHERE b > 822 ORDER BY c LIMIT 5;
+-----+-----+---+----------------------------------+
| a   | b   | c | d                                |
+-----+-----+---+----------------------------------+
| 838 | 837 | 0 | b0b183c207f46f0cca7dc63b2604f5cc |
| 854 | 853 | 0 | aff1621254f7c1be92f64550478c56e6 |
| 858 | 857 | 0 | 847cc55b7032108eee6dd897f3bca8a5 |
| 870 | 869 | 0 | 49c9adb18e44be0711a94e827042f630 |
| 892 | 891 | 0 | cfbce4c1d7c425baf21d6b6f2babe6be |
+-----+-----+---+----------------------------------+
5 rows in set (19.13 sec)

mysql> SELECT SQL_NO_CACHE * FROM count_test WHERE b > 822 ORDER BY c LIMIT 5;
+--------+-----+---+----------------------------------+
| a      | b   | c | d                                |
+--------+-----+---+----------------------------------+
|  46824 | 823 | 0 | cd5d786626b990d098e0407af7478475 |
|  66824 | 823 | 0 | 59acf66ee9debf6a5e9dcff92994afc2 |
| 103824 | 823 | 0 | 0319c74d26403fd24eb478680a467eff |
| 117824 | 823 | 0 | b6849cf6ad225b7c7e1b99940667859b |
| 134824 | 823 | 0 | 4941ea499aa4fc1290540bac50f8a067 |
+--------+-----+---+----------------------------------+
5 rows in set (2 min 23.90 sec)

mysql> SELECT SQL_NO_CACHE * FROM count_test WHERE b > 822 ORDER BY c LIMIT 5;
^CQuery aborted by Ctrl+C
ERROR 1028 (HY000): Sort aborted
mysql> SELECT SQL_NO_CACHE COUNT(*) FROM count_test WHERE b > 822;
+----------+
| COUNT(*) |
+----------+
|  1770001 |
+----------+
1 row in set (1.40 sec)

mysql> SELECT SQL_NO_CACHE SQL_CALC_FOUND_ROWS * FROM count_test WHERE b > 822 ORDER BY c LIMIT 5;
+-----+-----+---+----------------------------------+
| a   | b   | c | d                                |
+-----+-----+---+----------------------------------+
| 838 | 837 | 0 | b0b183c207f46f0cca7dc63b2604f5cc |
| 854 | 853 | 0 | aff1621254f7c1be92f64550478c56e6 |
| 858 | 857 | 0 | 847cc55b7032108eee6dd897f3bca8a5 |
| 870 | 869 | 0 | 49c9adb18e44be0711a94e827042f630 |
| 892 | 891 | 0 | cfbce4c1d7c425baf21d6b6f2babe6be |
+-----+-----+---+----------------------------------+
5 rows in set (20.06 sec)

mysql> SELECT SQL_NO_CACHE * FROM count_test WHERE b > 822 ORDER BY c LIMIT 5;
+--------+-----+---+----------------------------------+
| a      | b   | c | d                                |
+--------+-----+---+----------------------------------+
|  46824 | 823 | 0 | cd5d786626b990d098e0407af7478475 |
|  66824 | 823 | 0 | 59acf66ee9debf6a5e9dcff92994afc2 |
| 103824 | 823 | 0 | 0319c74d26403fd24eb478680a467eff |
| 117824 | 823 | 0 | b6849cf6ad225b7c7e1b99940667859b |
| 134824 | 823 | 0 | 4941ea499aa4fc1290540bac50f8a067 |
+--------+-----+---+----------------------------------+
5 rows in set (1 min 36.81 sec)

mysql> SELECT SQL_NO_CACHE SQL_CALC_FOUND_ROWS * FROM count_test WHERE b > 822 ORDER BY c LIMIT 5;
+-----+-----+---+----------------------------------+
| a   | b   | c | d                                |
+-----+-----+---+----------------------------------+
| 838 | 837 | 0 | b0b183c207f46f0cca7dc63b2604f5cc |
| 854 | 853 | 0 | aff1621254f7c1be92f64550478c56e6 |
| 858 | 857 | 0 | 847cc55b7032108eee6dd897f3bca8a5 |
| 870 | 869 | 0 | 49c9adb18e44be0711a94e827042f630 |
| 892 | 891 | 0 | cfbce4c1d7c425baf21d6b6f2babe6be |
+-----+-----+---+----------------------------------+
5 rows in set (19.74 sec)

mysql> SELECT SQL_NO_CACHE * FROM count_test WHERE b > 822 ORDER BY c LIMIT 5;
+--------+-----+---+----------------------------------+
| a      | b   | c | d                                |
+--------+-----+---+----------------------------------+
|  46824 | 823 | 0 | cd5d786626b990d098e0407af7478475 |
|  66824 | 823 | 0 | 59acf66ee9debf6a5e9dcff92994afc2 |
| 103824 | 823 | 0 | 0319c74d26403fd24eb478680a467eff |
| 117824 | 823 | 0 | b6849cf6ad225b7c7e1b99940667859b |
| 134824 | 823 | 0 | 4941ea499aa4fc1290540bac50f8a067 |
+--------+-----+---+----------------------------------+
5 rows in set (30.35 sec)
mysql> SELECT SQL_NO_CACHE SQL_CALC_FOUND_ROWS * FROM count_test WHERE b > 822 ORDER BY c LIMIT 5;
+-----+-----+---+----------------------------------+
| a   | b   | c | d                                |
+-----+-----+---+----------------------------------+
| 838 | 837 | 0 | b0b183c207f46f0cca7dc63b2604f5cc |
| 854 | 853 | 0 | aff1621254f7c1be92f64550478c56e6 |
| 858 | 857 | 0 | 847cc55b7032108eee6dd897f3bca8a5 |
| 870 | 869 | 0 | 49c9adb18e44be0711a94e827042f630 |
| 892 | 891 | 0 | cfbce4c1d7c425baf21d6b6f2babe6be |
+-----+-----+---+----------------------------------+
5 rows in set (20.52 sec)

Активист · 13 Дек 2010

Для особо неверующих - скриншот.

zerkms · 13 Дек 2010

И что в нём интересного? Правая часть индекса b+c не используется - потому сервер выгребает всё в память (или прям на винте) и сортирует.

zerkms · 13 Дек 2010

Неверующих в что? В то, что без индекса - будет омфг? Ну дык это же логично

Активист · 13 Дек 2010

Ну дк обясни, почему тогда запросы с SQL_CALC_FOUND_ROWS отрабатывают быстрее, иногда даже значительно?

zerkms · 13 Дек 2010

Активист
Разницу между запросами
SELECT SQL_NO_CACHE SQL_CALC_FOUND_ROWS * FROM count_test WHERE b > 822 ORDER BY c LIMIT 5;
и
SELECT SQL_NO_CACHE * FROM count_test WHERE b > 822 ORDER BY c LIMIT 5;

я никак разумно объяснить не могу. Но мне как минимум интересно, почему результаты этих запросов разные

Сделай запросы с фулсканом (или частичным сканом), но чтобы результаты были одинаковые (например добавь сортировку по PK, вместо `c`), тогда сравнение будет справедливее.

Активист · 13 Дек 2010

вот собственно

Код:

mysql> Aborted
linux-debian-1:/var/lib/mysql/test# /etc/init.d/mysql restart
Stopping MySQL database server: mysqld.
Starting MySQL database server: mysqld.
Checking for corrupt, not cleanly closed and upgrade needing tables..
linux-debian-1:/var/lib/mysql/test# mysql -u su1234 -A
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.0.51a-24+lenny4 (Debian)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> use test;
Database changed
mysql> SELECT SQL_NO_CACHE SQL_CALC_FOUND_ROWS * FROM count_test WHERE b > 822 ORDER BY c LIMIT 5;
+-----+-----+---+----------------------------------+
| a   | b   | c | d                                |
+-----+-----+---+----------------------------------+
| 838 | 837 | 0 | b0b183c207f46f0cca7dc63b2604f5cc |
| 854 | 853 | 0 | aff1621254f7c1be92f64550478c56e6 |
| 858 | 857 | 0 | 847cc55b7032108eee6dd897f3bca8a5 |
| 870 | 869 | 0 | 49c9adb18e44be0711a94e827042f630 |
| 892 | 891 | 0 | cfbce4c1d7c425baf21d6b6f2babe6be |
+-----+-----+---+----------------------------------+
5 rows in set (27.55 sec)

mysql> SELECT SQL_NO_CACHE * FROM count_test WHERE b > 822 ORDER BY c LIMIT 5;
+--------+-----+---+----------------------------------+
| a      | b   | c | d                                |
+--------+-----+---+----------------------------------+
|  46824 | 823 | 0 | cd5d786626b990d098e0407af7478475 |
|  66824 | 823 | 0 | 59acf66ee9debf6a5e9dcff92994afc2 |
| 103824 | 823 | 0 | 0319c74d26403fd24eb478680a467eff |
| 117824 | 823 | 0 | b6849cf6ad225b7c7e1b99940667859b |
| 134824 | 823 | 0 | 4941ea499aa4fc1290540bac50f8a067 |
+--------+-----+---+----------------------------------+
5 rows in set (2 min 2.36 sec)

mysql> SELECT SQL_NO_CACHE COUNT(*) FROM count_test WHERE b > 822 ORDER BY c LIMIT 5;
+----------+
| COUNT(*) |
+----------+
|  1770001 |
+----------+
1 row in set (1.20 sec)
mysql>

Значит не всегда проигрывает?

Активист · 13 Дек 2010

Что-то я окончательно запутался ))))

zerkms · 13 Дек 2010

Значит не всегда проигрывает?

Сначала результаты одинаковые получи, потом делай выводы

ORDER BY `a` DESC, например, вместо `c`

zerkms · 13 Дек 2010

Распутывайся

Вот два запроса, выполни:

SELECT SQL_NO_CACHE * FROM count_test WHERE b > 822 ORDER BY a DESC LIMIT 5;
SELECT SQL_NO_CACHE SQL_CALC_FOUND_ROWS * FROM count_test WHERE b > 822 ORDER BY a DESC LIMIT 5;

Активист · 13 Дек 2010

Еще больше запустался )))

Код:

mysql> Bye
linux-debian-1:/var/lib/mysql/test# /etc/init.d/mysql restart
Stopping MySQL database server: mysqld.
Starting MySQL database server: mysqld.
Checking for corrupt, not cleanly closed and upgrade needing tables..
linux-debian-1:/var/lib/mysql/test# mysql -u su1234 -D test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 5.0.51a-24+lenny4 (Debian)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> SELECT SQL_NO_CACHE * FROM count_test WHERE b > 822 ORDER BY a DESC LIMIT 5;
+---------+-----+----+----------------------------------+
| a       | b   | c  | d                                |
+---------+-----+----+----------------------------------+
| 9999999 | 999 |  4 | 283f42764da6dba2522412916b031080 |
| 9999998 | 999 | 10 | 283f42764da6dba2522412916b031080 |
| 9999997 | 998 |  3 | facf1f36bbcf6f3cefd7429e61999d26 |
| 9999996 | 997 |  6 | 305ebab8b5f24b0947b25a95f003b93a |
| 9999995 | 996 |  2 | 74ab43aba681049b7e20b601ccfc9cfc |
+---------+-----+----+----------------------------------+
5 rows in set (3 min 30.20 sec)

mysql> Bye
linux-debian-1:/var/lib/mysql/test# /etc/init.d/mysql restart
Stopping MySQL database server: mysqld.
Starting MySQL database server: mysqld.
Checking for corrupt, not cleanly closed and upgrade needing tables..
linux-debian-1:/var/lib/mysql/test# mysql -u su1234 -D test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 5.0.51a-24+lenny4 (Debian)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> SELECT SQL_NO_CACHE SQL_CALC_FOUND_ROWS * FROM count_test WHERE b > 822 ORDER BY a DESC LIMIT 5;
+---------+-----+----+----------------------------------+
| a       | b   | c  | d                                |
+---------+-----+----+----------------------------------+
| 9999999 | 999 |  4 | 283f42764da6dba2522412916b031080 |
| 9999998 | 999 | 10 | 283f42764da6dba2522412916b031080 |
| 9999997 | 998 |  3 | facf1f36bbcf6f3cefd7429e61999d26 |
| 9999996 | 997 |  6 | 305ebab8b5f24b0947b25a95f003b93a |
| 9999995 | 996 |  2 | 74ab43aba681049b7e20b601ccfc9cfc |
+---------+-----+----+----------------------------------+
5 rows in set (29.39 sec)

mysql> select count(*) from `count_test`
    -> ;
+----------+
| count(*) |
+----------+
| 10000000 |
+----------+
1 row in set (0.00 sec)

mysql> show create table `count_test`;
+------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table      | Create Table                                                                                                                                                                                                                                             |
+------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| count_test | CREATE TABLE `count_test` (
  `a` int(10) NOT NULL auto_increment,
  `b` int(10) NOT NULL,
  `c` int(10) NOT NULL,
  `d` varchar(32) NOT NULL,
  PRIMARY KEY  (`a`),
  KEY `bc` (`b`,`c`)
) ENGINE=MyISAM AUTO_INCREMENT=10000001 DEFAULT CHARSET=cp1251 |
+------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql>

Активист · 13 Дек 2010

MySQL установлен из репозитория

zerkms · 13 Дек 2010

Дисковый кэш может чуток вывез таки?
Так или иначе - вот этот результат я объяснить не могу уже никак, да он и относится уже скорее не к COUNT() vs SQL_CALC_FOUND_ROWS, а к "какого хера?!?!?"

Активист · 13 Дек 2010

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

Код:

linux-debian-1:/var/lib/mysql/test# mysql -u su1234 -D test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2242
Server version: 5.0.51a-24+lenny4 (Debian)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> EXPLAIN SELECT SQL_NO_CACHE SQL_CALC_FOUND_ROWS * FROM count_test WHERE b > 822 ORDER BY a DESC LIMIT 5;
+----+-------------+------------+------+---------------+------+---------+------+----------+-----------------------------+
| id | select_type | table      | type | possible_keys | key  | key_len | ref  | rows     | Extra                       |
+----+-------------+------------+------+---------------+------+---------+------+----------+-----------------------------+
|  1 | SIMPLE      | count_test | ALL  | bc            | NULL | NULL    | NULL | 10000000 | Using where; Using filesort |
+----+-------------+------------+------+---------------+------+---------+------+----------+-----------------------------+
1 row in set (0.07 sec)

mysql> EXPLAIN SELECT SQL_NO_CACHE * FROM count_test WHERE b > 822 ORDER BY a DESC LIMIT 5;
+----+-------------+------------+-------+---------------+------+---------+------+---------+-----------------------------+
| id | select_type | table      | type  | possible_keys | key  | key_len | ref  | rows    | Extra                       |
+----+-------------+------------+-------+---------------+------+---------+------+---------+-----------------------------+
|  1 | SIMPLE      | count_test | range | bc            | bc   | 4       | NULL | 5490412 | Using where; Using filesort |
+----+-------------+------------+-------+---------------+------+---------+------+---------+-----------------------------+
1 row in set (0.01 sec)

mysql> SELECT SQL_NO_CACHE * FROM count_test WHERE b > 822 ORDER BY a DESC LIMIT 5;
+---------+-----+----+----------------------------------+
| a       | b   | c  | d                                |
+---------+-----+----+----------------------------------+
| 9999999 | 999 |  4 | 283f42764da6dba2522412916b031080 |
| 9999998 | 999 | 10 | 283f42764da6dba2522412916b031080 |
| 9999997 | 998 |  3 | facf1f36bbcf6f3cefd7429e61999d26 |
| 9999996 | 997 |  6 | 305ebab8b5f24b0947b25a95f003b93a |
| 9999995 | 996 |  2 | 74ab43aba681049b7e20b601ccfc9cfc |
+---------+-----+----+----------------------------------+
5 rows in set (1 min 30.41 sec)

mysql> SELECT SQL_NO_CACHE SQL_CALC_FOUND_ROWS * FROM count_test WHERE b > 822 ORDER BY a DESC LIMIT 5;
+---------+-----+----+----------------------------------+
| a       | b   | c  | d                                |
+---------+-----+----+----------------------------------+
| 9999999 | 999 |  4 | 283f42764da6dba2522412916b031080 |
| 9999998 | 999 | 10 | 283f42764da6dba2522412916b031080 |
| 9999997 | 998 |  3 | facf1f36bbcf6f3cefd7429e61999d26 |
| 9999996 | 997 |  6 | 305ebab8b5f24b0947b25a95f003b93a |
| 9999995 | 996 |  2 | 74ab43aba681049b7e20b601ccfc9cfc |
+---------+-----+----+----------------------------------+
5 rows in set (17.37 sec)

mysql>

Активист · 13 Дек 2010

Собственно

Код:

linux-debian-1:~# mysqlcheck -u su1234 test
test.bannersGroups                                 OK
test.bannersItems                                  OK
test.boardGroups                                   OK
test.boardItems                                    OK
test.catalogGroups                                 OK
test.catalogGroupsFiles                            OK
test.catalogGroupsPhotos                           OK
test.catalogProducts                               OK
test.catalogProductsFiles                          OK
test.catalogProductsPhotos                         OK
test.count_test                                    OK
test.installedModules                              OK
test.intest                                        OK
test.news                                          OK
test.pages                                         OK
test.stat_hostsStat                                OK
test.test                                          OK
linux-debian-1:~#

Активист · 13 Дек 2010

Видимо что-то с кешем.

Код:

linux-debian-1:/var/lib/mysql/test# /etc/init.d/mysql stop
Stopping MySQL database server: mysqld.
linux-debian-1:/var/lib/mysql/test# start /etc/init.d/mysql stop
-bash: start: команда не найдена
linux-debian-1:/var/lib/mysql/test# /etc/init.d/mysql start
Starting MySQL database server: mysqld.
Checking for corrupt, not cleanly closed and upgrade needing tables..
linux-debian-1:/var/lib/mysql/test# mysql -u su1234 -D test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.0.51a-24+lenny4 (Debian)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> SELECT SQL_NO_CACHE SQL_CALC_FOUND_ROWS * FROM count_test WHERE b > 822  LIMIT 5;
+--------+-----+---+----------------------------------+
| a      | b   | c | d                                |
+--------+-----+---+----------------------------------+
|  46824 | 823 | 0 | cd5d786626b990d098e0407af7478475 |
|  66824 | 823 | 0 | 59acf66ee9debf6a5e9dcff92994afc2 |
| 103824 | 823 | 0 | 0319c74d26403fd24eb478680a467eff |
| 117824 | 823 | 0 | b6849cf6ad225b7c7e1b99940667859b |
| 134824 | 823 | 0 | 4941ea499aa4fc1290540bac50f8a067 |
+--------+-----+---+----------------------------------+
5 rows in set (2 min 44.55 sec)

mysql> SELECT SQL_NO_CACHE * FROM count_test WHERE b > 822  LIMIT 5;
+--------+-----+---+----------------------------------+
| a      | b   | c | d                                |
+--------+-----+---+----------------------------------+
|  46824 | 823 | 0 | cd5d786626b990d098e0407af7478475 |
|  66824 | 823 | 0 | 59acf66ee9debf6a5e9dcff92994afc2 |
| 103824 | 823 | 0 | 0319c74d26403fd24eb478680a467eff |
| 117824 | 823 | 0 | b6849cf6ad225b7c7e1b99940667859b |
| 134824 | 823 | 0 | 4941ea499aa4fc1290540bac50f8a067 |
+--------+-----+---+----------------------------------+
5 rows in set (0.01 sec)

mysql> SELECT SQL_NO_CACHE * FROM count_test WHERE b > 822  LIMIT 5;
+--------+-----+---+----------------------------------+
| a      | b   | c | d                                |
+--------+-----+---+----------------------------------+
|  46824 | 823 | 0 | cd5d786626b990d098e0407af7478475 |
|  66824 | 823 | 0 | 59acf66ee9debf6a5e9dcff92994afc2 |
| 103824 | 823 | 0 | 0319c74d26403fd24eb478680a467eff |
| 117824 | 823 | 0 | b6849cf6ad225b7c7e1b99940667859b |
| 134824 | 823 | 0 | 4941ea499aa4fc1290540bac50f8a067 |
+--------+-----+---+----------------------------------+
5 rows in set (0.00 sec)

mysql> SELECT SQL_NO_CACHE SQL_CALC_FOUND_ROWS * FROM count_test WHERE b > 822  LIMIT 5;
+--------+-----+---+----------------------------------+
| a      | b   | c | d                                |
+--------+-----+---+----------------------------------+
|  46824 | 823 | 0 | cd5d786626b990d098e0407af7478475 |
|  66824 | 823 | 0 | 59acf66ee9debf6a5e9dcff92994afc2 |
| 103824 | 823 | 0 | 0319c74d26403fd24eb478680a467eff |
| 117824 | 823 | 0 | b6849cf6ad225b7c7e1b99940667859b |
| 134824 | 823 | 0 | 4941ea499aa4fc1290540bac50f8a067 |
+--------+-----+---+----------------------------------+
5 rows in set (6.33 sec)

mysql> SELECT SQL_NO_CACHE SQL_CALC_FOUND_ROWS * FROM count_test WHERE b > 822  LIMIT 5;
+--------+-----+---+----------------------------------+
| a      | b   | c | d                                |
+--------+-----+---+----------------------------------+
|  46824 | 823 | 0 | cd5d786626b990d098e0407af7478475 |
|  66824 | 823 | 0 | 59acf66ee9debf6a5e9dcff92994afc2 |
| 103824 | 823 | 0 | 0319c74d26403fd24eb478680a467eff |
| 117824 | 823 | 0 | b6849cf6ad225b7c7e1b99940667859b |
| 134824 | 823 | 0 | 4941ea499aa4fc1290540bac50f8a067 |
+--------+-----+---+----------------------------------+
5 rows in set (5.60 sec)

mysql>

разбивка по страницам

zerkms

TDD infected

Активист

Активист

Активист

Активист

zerkms

TDD infected

zerkms

TDD infected

Активист

Активист

zerkms

TDD infected

Активист

Активист

Активист

Активист

zerkms

TDD infected

zerkms

TDD infected

Активист

Активист

Активист

Активист

zerkms

TDD infected

Активист

Активист

Активист

Активист

Активист

Активист