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)
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>
Сначала результаты одинаковые получи, потом делай выводыЗначит не всегда проигрывает?
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>
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>