1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104
| --------------
START TRANSACTION
--------------
--------------
set session collation_connection = "latin1_general_ci"
--------------
--------------
DROP DATABASE IF EXISTS `base`
--------------
--------------
CREATE DATABASE IF NOT EXISTS `base`
DEFAULT CHARACTER SET `latin1`
DEFAULT COLLATE `latin1_general_ci`
--------------
--------------
DROP TABLE IF EXISTS `test`
--------------
--------------
CREATE TABLE `test`
( `id` integer unsigned NOT NULL auto_increment primary key,
`val` smallint unsigned NOT NULL,
`lib` varchar(255) NOT NULL,
INDEX `idx1` USING BTREE (`val`)
) ENGINE=InnoDB
DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
ROW_FORMAT=COMPRESSED
--------------
--------------
INSERT INTO `test` (`lib`,`val`) values
('un', 1),('deux', 2),('trois', 3),('quatre', 4),('cinq', 5),
('eins', 1),('zwei', 2),('drei', 3),('vier', 4),('funf', 5),
('one', 1),('two', 2),('three', 3),('four', 4),('five', 5)
--------------
--------------
select * from `test`
--------------
+----+-----+--------+
| id | val | lib |
+----+-----+--------+
| 1 | 1 | un |
| 2 | 2 | deux |
| 3 | 3 | trois |
| 4 | 4 | quatre |
| 5 | 5 | cinq |
| 6 | 1 | eins |
| 7 | 2 | zwei |
| 8 | 3 | drei |
| 9 | 4 | vier |
| 10 | 5 | funf |
| 11 | 1 | one |
| 12 | 2 | two |
| 13 | 3 | three |
| 14 | 4 | four |
| 15 | 5 | five |
+----+-----+--------+
--------------
explain select * from `test` where val = 2
--------------
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
| 1 | SIMPLE | test | NULL | ref | idx1 | idx1 | 2 | const | 3 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
--------------
explain select * from `test` ignore index (`idx1`) where val = 2
--------------
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | test | NULL | ALL | NULL | NULL | NULL | NULL | 15 | 6.67 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
--------------
explain select * from `test` where val > 1
--------------
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | test | NULL | ALL | idx1 | NULL | NULL | NULL | 15 | 80.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
--------------
explain select * from `test` force index (`idx1`) where val > 1
--------------
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+
| 1 | SIMPLE | test | NULL | range | idx1 | idx1 | 2 | NULL | 12 | 100.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+
--------------
COMMIT
--------------
Appuyez sur une touche pour continuer... |
Partager