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

zerkms

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

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

м?
 

Активист

Активист
Команда форума
Хм, как оказалось - не всегда, вот еще интересней результат, странно....
Код:
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)
 

zerkms

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

zerkms

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

Активист

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

zerkms

TDD infected
Команда форума
Активист
Разницу между запросами
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`), тогда сравнение будет справедливее.
 

Активист

Активист
Команда форума
вот собственно
Код:
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>
Значит не всегда проигрывает?
 

zerkms

TDD infected
Команда форума
Распутывайся :) Вот два запроса, выполни:

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;
 

Активист

Активист
Команда форума
Еще больше запустался )))

Код:
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>
 

zerkms

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

Активист

Активист
Команда форума
Именно, какого хера)) И результаты главное разные. Даже по експлейну вроде как 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>
 

Активист

Активист
Команда форума
Собственно
Код:
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:~#
 

Активист

Активист
Команда форума
Видимо что-то с кешем.

Код:
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>
 
Сверху