CREATE TABLE `user` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(255) NOT NULL, `sex` tinyint(3) unsigned NOT NULL, `age` tinyint(3) unsigned NOT NULL, `email` varchar(255) NOT NULL, `address` varchar(350) NOT NULL, `company` varchar(255) NOT NULL, `city` varchar(255) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8
select count(*) from user; +----------+ | count(*) | +----------+ | 5037343 | +----------+ 1 row in set (1.81 sec)
select * from user limit 1 \G *************************** 1. row *************************** id: 1 name: Prof. Osborne Waelchi I sex: 0 age: 60 email: dach.angela@yahoo.com address: 35712 Quigley Mountains North Alysonville, CO 53682-2718 company: McGlynn Ltd city: Port Maziebury 1 row in set (0.01 sec)
MySQL [test_db_for_index]> show index from user; +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | user | 0 | PRIMARY | 1 | id | A | 4870574 | NULL | NULL | | BTREE | | | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 1 row in set (0.01 sec)
MySQL [test_db_for_index]> explain select * from user where id+1=2; +----+-------------+-------+------+---------------+------+---------+------+---------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+---------+-------------+ | 1 | SIMPLE | user | ALL | NULL | NULL | NULL | NULL | 4870574 | Using where | +----+-------------+-------+------+---------------+------+---------+------+---------+-------------+ 1 row in set (0.04 sec)
MySQL [test_db_for_index]> select * from user where id+1=2; +----+-------------------------+-----+-----+-----------------------+-----------------------------------------------------------+-------------+----------------+ | id | name | sex | age | email | address | company | city | +----+-------------------------+-----+-----+-----------------------+-----------------------------------------------------------+-------------+----------------+ | 1 | Prof. Osborne Waelchi I | 0 | 60 | dach.angela@yahoo.com | 35712 Quigley Mountains North Alysonville, CO 53682-2718 | McGlynn Ltd | Port Maziebury | +----+-------------------------+-----+-----+-----------------------+-----------------------------------------------------------+-------------+----------------+ 1 row in set (3.04 sec)
MySQL [test_db_for_index]> select * from user where id=1; +----+-------------------------+-----+-----+-----------------------+-----------------------------------------------------------+-------------+----------------+ | id | name | sex | age | email | address | company | city | +----+-------------------------+-----+-----+-----------------------+-----------------------------------------------------------+-------------+----------------+ | 1 | Prof. Osborne Waelchi I | 0 | 60 | dach.angela@yahoo.com | 35712 Quigley Mountains North Alysonville, CO 53682-2718 | McGlynn Ltd | Port Maziebury | +----+-------------------------+-----+-----+-----------------------+-----------------------------------------------------------+-------------+----------------+ 1 row in set (0.01 sec)
MySQL [test_db_for_index]> show index from user; +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | user | 0 | PRIMARY | 1 | id | A | 4870574 | NULL | NULL | | BTREE | | | | user | 1 | sex_name | 1 | sex | A | 2 | NULL | NULL | | BTREE | | | | user | 1 | sex_name | 2 | name | A | 2435287 | NULL | NULL | | BTREE | | | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 3 rows in set (0.02 sec)
MySQL [test_db_for_index]> desc select * from user where name='Prof. Osborne Waelchi I'; +----+-------------+-------+------+---------------+------+---------+------+---------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+---------+-------------+ | 1 | SIMPLE | user | ALL | NULL | NULL | NULL | NULL | 4870574 | Using where | +----+-------------+-------+------+---------------+------+---------+------+---------+-------------+ 1 row in set (0.01 sec)
MySQL [test_db_for_index]> select * from user where name='Prof. Osborne Waelchi I'; +----+-------------------------+-----+-----+-----------------------+-----------------------------------------------------------+-------------+----------------+ | id | name | sex | age | email | address | company | city | +----+-------------------------+-----+-----+-----------------------+-----------------------------------------------------------+-------------+----------------+ | 1 | Prof. Osborne Waelchi I | 0 | 60 | dach.angela@yahoo.com | 35712 Quigley Mountains North Alysonville, CO 53682-2718 | McGlynn Ltd | Port Maziebury | +----+-------------------------+-----+-----+-----------------------+-----------------------------------------------------------+-------------+----------------+ 1 row in set (3.11 sec)
MySQL [test_db_for_index]> explain select * from user where name='Prof. Osborne Waelchi I' and sex in (0,1); +----+-------------+-------+-------+---------------+----------+---------+------+------+-----------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+----------+---------+------+------+-----------------------+ | 1 | SIMPLE | user | range | sex_name | sex_name | 768 | NULL | 2 | Using index condition | +----+-------------+-------+-------+---------------+----------+---------+------+------+-----------------------+ 1 row in set (0.01 sec)
MySQL [test_db_for_index]> select * from user where name='Prof. Osborne Waelchi I' and sex in (0,1); +----+-------------------------+-----+-----+-----------------------+-----------------------------------------------------------+-------------+----------------+ | id | name | sex | age | email | address | company | city | +----+-------------------------+-----+-----+-----------------------+-----------------------------------------------------------+-------------+----------------+ | 1 | Prof. Osborne Waelchi I | 0 | 60 | dach.angela@yahoo.com | 35712 Quigley Mountains North Alysonville, CO 53682-2718 | McGlynn Ltd | Port Maziebury | +----+-------------------------+-----+-----+-----------------------+-----------------------------------------------------------+-------------+----------------+ 1 row in set (0.01 sec)
MySQL [test_db_for_index]> show index from user; +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | user | 0 | PRIMARY | 1 | id | A | 4870574 | NULL | NULL | | BTREE | | | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 1 row in set (0.01 sec)
MySQL [test_db_for_index]> desc select * from user where name='Prof. Osborne Waelchi I' and age=60; +----+-------------+-------+------+---------------+------+---------+------+---------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+---------+-------------+ | 1 | SIMPLE | user | ALL | NULL | NULL | NULL | NULL | 4870574 | Using where | +----+-------------+-------+------+---------------+------+---------+------+---------+-------------+ 1 row in set (0.03 sec)
MySQL [test_db_for_index]> desc select * from user where name='Prof. Osborne Waelchi I'; +----+-------------+-------+------+---------------+------+---------+------+---------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+---------+-------------+ | 1 | SIMPLE | user | ALL | NULL | NULL | NULL | NULL | 4870574 | Using where | +----+-------------+-------+------+---------------+------+---------+------+---------+-------------+ 1 row in set (0.02 sec)
MySQL [test_db_for_index]> select * from user where name='Prof. Osborne Waelchi I' and age=60;; +----+-------------------------+-----+-----+-----------------------+-----------------------------------------------------------+-------------+----------------+ | id | name | sex | age | email | address | company | city | +----+-------------------------+-----+-----+-----------------------+-----------------------------------------------------------+-------------+----------------+ | 1 | Prof. Osborne Waelchi I | 0 | 60 | dach.angela@yahoo.com | 35712 Quigley Mountains North Alysonville, CO 53682-2718 | McGlynn Ltd | Port Maziebury | +----+-------------------------+-----+-----+-----------------------+-----------------------------------------------------------+-------------+----------------+ 1 row in set (3.03 sec)
ERROR: No query specified
MySQL [test_db_for_index]> select * from user where name='Prof. Osborne Waelchi I'; +----+-------------------------+-----+-----+-----------------------+-----------------------------------------------------------+-------------+----------------+ | id | name | sex | age | email | address | company | city | +----+-------------------------+-----+-----+-----------------------+-----------------------------------------------------------+-------------+----------------+ | 1 | Prof. Osborne Waelchi I | 0 | 60 | dach.angela@yahoo.com | 35712 Quigley Mountains North Alysonville, CO 53682-2718 | McGlynn Ltd | Port Maziebury | +----+-------------------------+-----+-----+-----------------------+-----------------------------------------------------------+-------------+----------------+ 1 row in set (3.03 sec)
MySQL [test_db_for_index]> show index from user; +-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | user | 0 | PRIMARY | 1 | id | A | 4870574 | NULL | NULL | | BTREE | | | | user | 1 | name_age_email | 1 | name | A | 1623524 | NULL | NULL | | BTREE | | | | user | 1 | name_age_email | 2 | age | A | 2435287 | NULL | NULL | | BTREE | | | | user | 1 | name_age_email | 3 | email | A | 2435287 | NULL | NULL | | BTREE | | | +-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 4 rows in set (0.02 sec)
MySQL [test_db_for_index]> desc select * from user where name='Prof. Osborne Waelchi I'; +----+-------------+-------+------+----------------+----------------+---------+-------+------+-----------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+----------------+----------------+---------+-------+------+-----------------------+ | 1 | SIMPLE | user | ref | name_age_email | name_age_email | 767 | const | 1 | Using index condition | +----+-------------+-------+------+----------------+----------------+---------+-------+------+-----------------------+ 1 row in set (0.01 sec)
MySQL [test_db_for_index]> select * from user where name='Prof. Osborne Waelchi I'; +----+-------------------------+-----+-----+-----------------------+-----------------------------------------------------------+-------------+----------------+ | id | name | sex | age | email | address | company | city | +----+-------------------------+-----+-----+-----------------------+-----------------------------------------------------------+-------------+----------------+ | 1 | Prof. Osborne Waelchi I | 0 | 60 | dach.angela@yahoo.com | 35712 Quigley Mountains North Alysonville, CO 53682-2718 | McGlynn Ltd | Port Maziebury | +----+-------------------------+-----+-----+-----------------------+-----------------------------------------------------------+-------------+----------------+ 1 row in set (0.01 sec)
MySQL [test_db_for_index]> select * from user where name='Prof. Osborne Waelchi I' and age=60;; +----+-------------------------+-----+-----+-----------------------+-----------------------------------------------------------+-------------+----------------+ | id | name | sex | age | email | address | company | city | +----+-------------------------+-----+-----+-----------------------+-----------------------------------------------------------+-------------+----------------+ | 1 | Prof. Osborne Waelchi I | 0 | 60 | dach.angela@yahoo.com | 35712 Quigley Mountains North Alysonville, CO 53682-2718 | McGlynn Ltd | Port Maziebury | +----+-------------------------+-----+-----+-----------------------+-----------------------------------------------------------+-------------+----------------+ 1 row in set (0.01 sec)
ERROR: No query specified
MySQL [test_db_for_index]> desc select * from user where name='Prof. Osborne Waelchi I' and age=60; +----+-------------+-------+------+----------------+----------------+---------+-------------+------+-----------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+----------------+----------------+---------+-------------+------+-----------------------+ | 1 | SIMPLE | user | ref | name_age_email | name_age_email | 768 | const,const | 1 | Using index condition | +----+-------------+-------+------+----------------+----------------+---------+-------------+------+-----------------------+ 1 row in set (0.04 sec)
MySQL [test_db_for_index]> show index from user; +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | user | 0 | PRIMARY | 1 | id | A | 4870574 | NULL | NULL | | BTREE | | | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 1 row in set (0.02 sec)
MySQL [test_db_for_index]> desc select * from user where name like 'Prof. Osborne W%'; +----+-------------+-------+------+---------------+------+---------+------+---------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+---------+-------------+ | 1 | SIMPLE | user | ALL | NULL | NULL | NULL | NULL | 4870574 | Using where | +----+-------------+-------+------+---------------+------+---------+------+---------+-------------+ 1 row in set (0.02 sec)
MySQL [test_db_for_index]> select * from user where name like 'Prof. Osborne W%'; +---------+--------------------------+-----+-----+--------------------------+-----------------------------------------------------------+----------------+------------------+ | id | name | sex | age | email | address | company | city | +---------+--------------------------+-----+-----+--------------------------+-----------------------------------------------------------+----------------+------------------+ | 1 | Prof. Osborne Waelchi I | 0 | 60 | dach.angela@yahoo.com | 35712 Quigley Mountains North Alysonville, CO 53682-2718 | McGlynn Ltd | Port Maziebury | | 798465 | Prof. Osborne Weimann I | 0 | 79 | santino92@spinka.com | 64416 Lia Mills Apt. 784 Kleinburgh, MI 09030-1298 | Green PLC | Hesselhaven | | 1167101 | Prof. Osborne Weissnat V | 0 | 74 | monserrat36@shanahan.com | 77818 Rohan Throughway Koelpinmouth, VA 66568-0775 | Stark-Anderson | Oberbrunnershire | | 1660173 | Prof. Osborne Weimann I | 0 | 79 | santino92@spinka.com | 64416 Lia Mills Apt. 784 Kleinburgh, MI 09030-1298 | Green PLC | Hesselhaven | | 2160173 | Prof. Osborne Weimann I | 0 | 79 | santino92@spinka.com | 64416 Lia Mills Apt. 784 Kleinburgh, MI 09030-1298 | Green PLC | Hesselhaven | | 2660173 | Prof. Osborne Weimann I | 0 | 79 | santino92@spinka.com | 64416 Lia Mills Apt. 784 Kleinburgh, MI 09030-1298 | Green PLC | Hesselhaven | | 3160173 | Prof. Osborne Weimann I | 0 | 79 | santino92@spinka.com | 64416 Lia Mills Apt. 784 Kleinburgh, MI 09030-1298 | Green PLC | Hesselhaven | | 3528809 | Prof. Osborne Weissnat V | 0 | 74 | monserrat36@shanahan.com | 77818 Rohan Throughway Koelpinmouth, VA 66568-0775 | Stark-Anderson | Oberbrunnershire | | 4021968 | Prof. Osborne Weimann I | 0 | 79 | santino92@spinka.com | 64416 Lia Mills Apt. 784 Kleinburgh, MI 09030-1298 | Green PLC | Hesselhaven | | 4521968 | Prof. Osborne Weimann I | 0 | 79 | santino92@spinka.com | 64416 Lia Mills Apt. 784 Kleinburgh, MI 09030-1298 | Green PLC | Hesselhaven | | 5021968 | Prof. Osborne Weimann I | 0 | 79 | santino92@spinka.com | 64416 Lia Mills Apt. 784 Kleinburgh, MI 09030-1298 | Green PLC | Hesselhaven | +---------+--------------------------+-----+-----+--------------------------+-----------------------------------------------------------+----------------+------------------+ 11 rows in set (3.30 sec)
MySQL [test_db_for_index]> show index from user; +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | user | 0 | PRIMARY | 1 | id | A | 4870574 | NULL | NULL | | BTREE | | | | user | 1 | name | 1 | name | A | 1623524 | NULL | NULL | | BTREE | | | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 2 rows in set (0.02 sec)
MySQL [test_db_for_index]> desc select * from user where name like 'Prof. Osborne W%'; +----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+ | 1 | SIMPLE | user | range | name | name | 767 | NULL | 11 | Using index condition | +----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+ 1 row in set (0.02 sec)
MySQL [test_db_for_index]> select * from user where name like 'Prof. Osborne W%'; +---------+--------------------------+-----+-----+--------------------------+-----------------------------------------------------------+----------------+------------------+ | id | name | sex | age | email | address | company | city | +---------+--------------------------+-----+-----+--------------------------+-----------------------------------------------------------+----------------+------------------+ | 1 | Prof. Osborne Waelchi I | 0 | 60 | dach.angela@yahoo.com | 35712 Quigley Mountains North Alysonville, CO 53682-2718 | McGlynn Ltd | Port Maziebury | | 798465 | Prof. Osborne Weimann I | 0 | 79 | santino92@spinka.com | 64416 Lia Mills Apt. 784 Kleinburgh, MI 09030-1298 | Green PLC | Hesselhaven | | 1660173 | Prof. Osborne Weimann I | 0 | 79 | santino92@spinka.com | 64416 Lia Mills Apt. 784 Kleinburgh, MI 09030-1298 | Green PLC | Hesselhaven | | 2160173 | Prof. Osborne Weimann I | 0 | 79 | santino92@spinka.com | 64416 Lia Mills Apt. 784 Kleinburgh, MI 09030-1298 | Green PLC | Hesselhaven | | 2660173 | Prof. Osborne Weimann I | 0 | 79 | santino92@spinka.com | 64416 Lia Mills Apt. 784 Kleinburgh, MI 09030-1298 | Green PLC | Hesselhaven | | 3160173 | Prof. Osborne Weimann I | 0 | 79 | santino92@spinka.com | 64416 Lia Mills Apt. 784 Kleinburgh, MI 09030-1298 | Green PLC | Hesselhaven | | 4021968 | Prof. Osborne Weimann I | 0 | 79 | santino92@spinka.com | 64416 Lia Mills Apt. 784 Kleinburgh, MI 09030-1298 | Green PLC | Hesselhaven | | 4521968 | Prof. Osborne Weimann I | 0 | 79 | santino92@spinka.com | 64416 Lia Mills Apt. 784 Kleinburgh, MI 09030-1298 | Green PLC | Hesselhaven | | 5021968 | Prof. Osborne Weimann I | 0 | 79 | santino92@spinka.com | 64416 Lia Mills Apt. 784 Kleinburgh, MI 09030-1298 | Green PLC | Hesselhaven | | 1167101 | Prof. Osborne Weissnat V | 0 | 74 | monserrat36@shanahan.com | 77818 Rohan Throughway Koelpinmouth, VA 66568-0775 | Stark-Anderson | Oberbrunnershire | | 3528809 | Prof. Osborne Weissnat V | 0 | 74 | monserrat36@shanahan.com | 77818 Rohan Throughway Koelpinmouth, VA 66568-0775 | Stark-Anderson | Oberbrunnershire | +---------+--------------------------+-----+-----+--------------------------+-----------------------------------------------------------+----------------+------------------+ 11 rows in set (0.04 sec)
MySQL [test_db_for_index]> show index from user; +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | user | 0 | PRIMARY | 1 | id | A | 4870574 | NULL | NULL | | BTREE | | | | user | 1 | name | 1 | name | A | 1623524 | NULL | NULL | | BTREE | | | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 2 rows in set (0.01 sec)
MySQL [test_db_for_index]> desc select * from user where name='Prof. Osborne Waelchi I' or name='Zaria Quigley'; +----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+ | 1 | SIMPLE | user | range | name | name | 767 | NULL | 2 | Using index condition | +----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+ 1 row in set (0.02 sec)
MySQL [test_db_for_index]> desc select * from user where name in('Prof. Osborne Waelchi I','Zaria Quigley'); +----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+ | 1 | SIMPLE | user | range | name | name | 767 | NULL | 2 | Using index condition | +----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+ 1 row in set (0.01 sec)
MySQL [test_db_for_index]> select * from user where name in('Prof. Osborne Waelchi I','Zaria Quigley'); +----+-------------------------+-----+-----+------------------------+-----------------------------------------------------------+------------------+----------------+ | id | name | sex | age | email | address | company | city | +----+-------------------------+-----+-----+------------------------+-----------------------------------------------------------+------------------+----------------+ | 1 | Prof. Osborne Waelchi I | 0 | 60 | dach.angela@yahoo.com | 35712 Quigley Mountains North Alysonville, CO 53682-2718 | McGlynn Ltd | Port Maziebury | | 4 | Zaria Quigley | 0 | 41 | ryan.anissa@cronin.com | 799 Barney Cove Princessland, VA 34382 | Farrell-Hartmann | DuBuqueport | +----+-------------------------+-----+-----+------------------------+-----------------------------------------------------------+------------------+----------------+ 2 rows in set (0.01 sec)
MySQL [test_db_for_index]> select * from user where name='Prof. Osborne Waelchi I' or name='Zaria Quigley'; +----+-------------------------+-----+-----+------------------------+-----------------------------------------------------------+------------------+----------------+ | id | name | sex | age | email | address | company | city | +----+-------------------------+-----+-----+------------------------+-----------------------------------------------------------+------------------+----------------+ | 1 | Prof. Osborne Waelchi I | 0 | 60 | dach.angela@yahoo.com | 35712 Quigley Mountains North Alysonville, CO 53682-2718 | McGlynn Ltd | Port Maziebury | | 4 | Zaria Quigley | 0 | 41 | ryan.anissa@cronin.com | 799 Barney Cove Princessland, VA 34382 | Farrell-Hartmann | DuBuqueport | +----+-------------------------+-----+-----+------------------------+-----------------------------------------------------------+------------------+----------------+ 2 rows in set (0.01 sec)
MySQL [test_db_for_index]> show index from user; +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | user | 0 | PRIMARY | 1 | id | A | 4870574 | NULL | NULL | | BTREE | | | | user | 1 | name | 1 | name | A | 1623524 | NULL | NULL | | BTREE | | | | user | 1 | email | 1 | email | A | 1623524 | NULL | NULL | | BTREE | | | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 3 rows in set (0.01 sec)
MySQL [test_db_for_index]> desc select * from user where name='1' or email='d'; +----+-------------+-------+-------------+---------------+------------+---------+------+------+--------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------------+---------------+------------+---------+------+------+--------------------------------------+ | 1 | SIMPLE | user | index_merge | name,email | name,email | 767,767 | NULL | 2 | Using union(name,email); Using where | +----+-------------+-------+-------------+---------------+------------+---------+------+------+--------------------------------------+ 1 row in set (0.01 sec)
MySQL [test_db_for_index]> select * from user where name='1' or email='d'; Empty set (0.02 sec)
MySQL [test_db_for_index]> select * from user where name='1' union select * from user where email='d'; Empty set (0.01 sec)
MySQL [test_db_for_index]> desc select * from user where name='1' union select * from user where email='d'; +----+--------------+------------+------+---------------+-------+---------+-------+------+-----------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+--------------+------------+------+---------------+-------+---------+-------+------+-----------------------+ | 1 | PRIMARY | user | ref | name | name | 767 | const | 1 | Using index condition | | 2 | UNION | user | ref | email | email | 767 | const | 1 | Using index condition | | NULL | UNION RESULT | <union1,2> | ALL | NULL | NULL | NULL | NULL | NULL | Using temporary | +----+--------------+------------+------+---------------+-------+---------+-------+------+-----------------------+ 3 rows in set (0.01 sec)
## sex字段只有 0 1 两个取值 MySQL [test_db_for_index]> show index from user; +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | user | 0 | PRIMARY | 1 | id | A | 4870574 | NULL | NULL | | BTREE | | | | user | 1 | sex_name | 1 | sex | A | 2 | NULL | NULL | | BTREE | | | | user | 1 | sex_name | 2 | name | A | 2435287 | NULL | NULL | | BTREE | | | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 3 rows in set (0.01 sec)
MySQL [test_db_for_index]> desc select * from user where sex!=1 and name='payton'; +----+-------------+-------+------+---------------+------+---------+------+---------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+---------+-------------+ | 1 | SIMPLE | user | ALL | sex_name | NULL | NULL | NULL | 4870574 | Using where | +----+-------------+-------+------+---------------+------+---------+------+---------+-------------+ 1 row in set (0.01 sec)
MySQL [test_db_for_index]> select * from user where sex!=1 and name='payton'; Empty set (3.18 sec)
MySQL [test_db_for_index]> desc select * from user where sex=0 and name='payton'; +----+-------------+-------+------+---------------+----------+---------+-------------+------+-----------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+----------+---------+-------------+------+-----------------------+ | 1 | SIMPLE | user | ref | sex_name | sex_name | 768 | const,const | 1 | Using index condition | +----+-------------+-------+------+---------------+----------+---------+-------------+------+-----------------------+ 1 row in set (0.02 sec)
MySQL [test_db_for_index]> select * from user where sex=0 and name='payton'; Empty set (0.02 sec)
MySQL [test_db_for_index]> show index from user; +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | user | 0 | PRIMARY | 1 | id | A | 4870574 | NULL | NULL | | BTREE | | | | user | 1 | age_name | 1 | age | A | 136 | NULL | NULL | | BTREE | | | | user | 1 | age_name | 2 | name | A | 2435287 | NULL | NULL | | BTREE | | | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 3 rows in set (0.01 sec)
MySQL [test_db_for_index]> desc select * from user where age >= 10 and age <= 15 and name='payton'; +----+-------------+-------+-------+---------------+----------+---------+------+--------+-----------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+----------+---------+------+--------+-----------------------+ | 1 | SIMPLE | user | range | age_name | age_name | 768 | NULL | 626654 | Using index condition | +----+-------------+-------+-------+---------------+----------+---------+------+--------+-----------------------+ 1 row in set (0.01 sec)
MySQL [test_db_for_index]> select * from user where age >= 10 and age <= 15 and name='payton'; Empty set (0.09 sec)
MySQL [test_db_for_index]> desc select * from user where age in (10,11,12,13,14,15) and name='payton'; +----+-------------+-------+-------+---------------+----------+---------+------+------+-----------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+----------+---------+------+------+-----------------------+ | 1 | SIMPLE | user | range | age_name | age_name | 768 | NULL | 6 | Using index condition | +----+-------------+-------+-------+---------------+----------+---------+------+------+-----------------------+ 1 row in set (0.01 sec)
MySQL [test_db_for_index]> select * from user where age in (10,11,12,13,14,15) and name='payton'; Empty set (0.02 sec)
MySQL [test_db_for_index]> desc select count(*) from user; +----+-------------+-------+-------+---------------+---------+---------+------+---------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+---------+---------+------+---------+-------------+ | 1 | SIMPLE | user | index | NULL | PRIMARY | 4 | NULL | 4870574 | Using index | +----+-------------+-------+-------+---------------+---------+---------+------+---------+-------------+ 1 row in set (0.02 sec)
MySQL [test_db_for_index]> desc select count(*) from user; +----+-------------+-------+-------+---------------+---------+---------+------+---------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+---------+---------+------+---------+-------------+ | 1 | SIMPLE | user | index | NULL | PRIMARY | 4 | NULL | 4870574 | Using index | +----+-------------+-------+-------+---------------+---------+---------+------+---------+-------------+ 1 row in set (0.01 sec)
MySQL [test_db_for_index]> select count(*) from user; +----------+ | count(*) | +----------+ | 5037343 | +----------+ 1 row in set (1.69 sec)
MySQL [test_db_for_index]> show index from user; +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | user | 0 | PRIMARY | 1 | id | A | 4870574 | NULL | NULL | | BTREE | | | | user | 1 | sex | 1 | sex | A | 2 | NULL | NULL | | BTREE | | | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 2 rows in set (0.01 sec)
MySQL [test_db_for_index]> desc select count(*) from user; +----+-------------+-------+-------+---------------+------+---------+------+---------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+------+---------+------+---------+-------------+ | 1 | SIMPLE | user | index | NULL | sex | 1 | NULL | 4870574 | Using index | +----+-------------+-------+-------+---------------+------+---------+------+---------+-------------+ 1 row in set (0.01 sec)
MySQL [test_db_for_index]> select count(*) from user; +----------+ | count(*) | +----------+ | 5037343 | +----------+ 1 row in set (0.67 sec)
InnoDB 缓存池的大小,对查询的影响
这个实验的环境和其他的不一样。
缓存池是存储引擎实现的。在 MySQL InnoDB 中,可以通过innodb_buffer_pool_size参数来定义缓存池的大小。
MySQL [test_db_for_index]> show index from user; +-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | user | 0 | PRIMARY | 1 | id | A | 4870574 | NULL | NULL | | BTREE | | | | user | 1 | age_name_email | 1 | age | A | 29698 | NULL | NULL | | BTREE | | | | user | 1 | age_name_email | 2 | name | A | 2435287 | NULL | NULL | | BTREE | | | | user | 1 | age_name_email | 3 | email | A | 2435287 | NULL | NULL | | BTREE | | | | user | 1 | name_age_email | 1 | name | A | 1623524 | NULL | NULL | | BTREE | | | | user | 1 | name_age_email | 2 | age | A | 1623524 | NULL | NULL | | BTREE | | | | user | 1 | name_age_email | 3 | email | A | 2435287 | NULL | NULL | | BTREE | | | +-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 7 rows in set (0.01 sec)
MySQL [test_db_for_index]> desc select age,name,email from user where age>1 order by name limit 1; +----+-------------+-------+-------+----------------+----------------+---------+------+------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+----------------+----------------+---------+------+------+--------------------------+ | 1 | SIMPLE | user | index | age_name_email | name_age_email | 1535 | NULL | 2 | Using where; Using index | +----+-------------+-------+-------+----------------+----------------+---------+------+------+--------------------------+ 1 row in set (0.01 sec)
MySQL [test_db_for_index]> desc select age,name,email from user where age>50 order by name limit 1; +----+-------------+-------+-------+----------------+----------------+---------+------+------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+----------------+----------------+---------+------+------+--------------------------+ | 1 | SIMPLE | user | index | age_name_email | name_age_email | 1535 | NULL | 2 | Using where; Using index | +----+-------------+-------+-------+----------------+----------------+---------+------+------+--------------------------+ 1 row in set (0.01 sec)
MySQL [test_db_for_index]> desc select age,name,email from user where age>70 order by name limit 1; +----+-------------+-------+-------+----------------+----------------+---------+------+------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+----------------+----------------+---------+------+------+--------------------------+ | 1 | SIMPLE | user | index | age_name_email | name_age_email | 1535 | NULL | 3 | Using where; Using index | +----+-------------+-------+-------+----------------+----------------+---------+------+------+--------------------------+
MySQL [test_db_for_index]> desc select age,name,email from user where age>80 order by name limit 1; +----+-------------+-------+-------+----------------+----------------+---------+------+------+------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+----------------+----------------+---------+------+------+------------------------------------------+ | 1 | SIMPLE | user | range | age_name_email | age_name_email | 1 | NULL | 1 | Using where; Using index; Using filesort | +----+-------------+-------+-------+----------------+----------------+---------+------+------+------------------------------------------+ 1 row in set (0.02 sec)
MySQL [test_db_for_index]> desc select age,name,email from user where age>580 order by name limit 1; +----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+ | 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE noticed after reading const tables | +----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+ 1 row in set (0.02 sec)
MySQL [test_db_for_index]> show index from user; +-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | user | 0 | PRIMARY | 1 | id | A | 4870574 | NULL | NULL | | BTREE | | | | user | 1 | age_name_email | 1 | age | A | 29698 | NULL | NULL | | BTREE | | | | user | 1 | age_name_email | 2 | name | A | 2435287 | NULL | NULL | | BTREE | | | | user | 1 | age_name_email | 3 | email | A | 2435287 | NULL | NULL | | BTREE | | | | user | 1 | name_age_email | 1 | name | A | 1623524 | NULL | NULL | | BTREE | | | | user | 1 | name_age_email | 2 | age | A | 1623524 | NULL | NULL | | BTREE | | | | user | 1 | name_age_email | 3 | email | A | 2435287 | NULL | NULL | | BTREE | | | +-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 7 rows in set (0.01 sec)
MySQL [test_db_for_index]> select age,name,email from user where age > 18 order by name limit 1; +-----+--------------------+-----------------+ | age | name | email | +-----+--------------------+-----------------+ | 60 | Aaliyah Altenwerth | grice@yahoo.com | +-----+--------------------+-----------------+ 1 row in set (0.01 sec)
MySQL [test_db_for_index]> select age,name,email from user force index(age_name_email) where age > 18 order by name limit 1; +-----+--------------------+-----------------+ | age | name | email | +-----+--------------------+-----------------+ | 60 | Aaliyah Altenwerth | grice@yahoo.com | +-----+--------------------+-----------------+ 1 row in set (3.11 sec)
MySQL [test_db_for_index]> select age,name,email from user force index(name_age_email) where age > 18 order by name limit 1; +-----+--------------------+-----------------+ | age | name | email | +-----+--------------------+-----------------+ | 60 | Aaliyah Altenwerth | grice@yahoo.com | +-----+--------------------+-----------------+ 1 row in set (0.00 sec)
MySQL [test_db_for_index]> select age,name,email from user force index(name_age_email) where age > 18 order by name limit 100000,10; +-----+--------------------+-------------------------------+ | age | name | email | +-----+--------------------+-------------------------------+ | 20 | Alexandrea Deckow | lueilwitz.barry@hermiston.com | | 20 | Alexandrea Deckow | lueilwitz.barry@hermiston.com | | 20 | Alexandrea Deckow | lueilwitz.barry@hermiston.com | | 20 | Alexandrea Deckow | lueilwitz.barry@hermiston.com | | 20 | Alexandrea Deckow | lueilwitz.barry@hermiston.com | | 20 | Alexandrea Deckow | lueilwitz.barry@hermiston.com | | 47 | Alexandrea Denesik | loy.larkin@durgan.com | | 47 | Alexandrea Denesik | loy.larkin@durgan.com | | 28 | Alexandrea Dibbert | rae61@gerhold.info | | 28 | Alexandrea Dibbert | rae61@gerhold.info | +-----+--------------------+-------------------------------+ 10 rows in set (0.06 sec)
MySQL [test_db_for_index]> select age,name,email from user force index(age_name_email) where age > 18 order by name limit 100000,10; +-----+--------------------+-------------------------------+ | age | name | email | +-----+--------------------+-------------------------------+ | 20 | Alexandrea Deckow | lueilwitz.barry@hermiston.com | | 20 | Alexandrea Deckow | lueilwitz.barry@hermiston.com | | 20 | Alexandrea Deckow | lueilwitz.barry@hermiston.com | | 20 | Alexandrea Deckow | lueilwitz.barry@hermiston.com | | 20 | Alexandrea Deckow | lueilwitz.barry@hermiston.com | | 20 | Alexandrea Deckow | lueilwitz.barry@hermiston.com | | 47 | Alexandrea Denesik | loy.larkin@durgan.com | | 47 | Alexandrea Denesik | loy.larkin@durgan.com | | 28 | Alexandrea Dibbert | rae61@gerhold.info | | 28 | Alexandrea Dibbert | rae61@gerhold.info | +-----+--------------------+-------------------------------+ 10 rows in set (18.65 sec)
--------------------------
MySQL [test_db_for_index]> select age,name,email from user force index(name_age_email) where age > 89 order by name limit 1; Empty set (1.61 sec)
MySQL [test_db_for_index]> select age,name,email from user force index(age_name_email) where age > 89 order by name limit 1; Empty set (0.01 sec)
MySQL [test_db_for_index]> select age,name,email from user force index(name_age_email) where age > 18 order by name limit 1; +-----+--------------------+-----------------+ | age | name | email | +-----+--------------------+-----------------+ | 60 | Aaliyah Altenwerth | grice@yahoo.com | +-----+--------------------+-----------------+ 1 row in set (0.00 sec)
MySQL [test_db_for_index]> select age,name,email from user force index(age_name_email) where age > 18 order by name limit 1; +-----+--------------------+-----------------+ | age | name | email | +-----+--------------------+-----------------+ | 60 | Aaliyah Altenwerth | grice@yahoo.com | +-----+--------------------+-----------------+ 1 row in set (3.11 sec)
MySQL [test_db_for_index]> show index from user; +-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | user | 0 | PRIMARY | 1 | id | A | 4870574 | NULL | NULL | | BTREE | | | | user | 1 | age_name_email | 1 | age | A | 29698 | NULL | NULL | | BTREE | | | | user | 1 | age_name_email | 2 | name | A | 2435287 | NULL | NULL | | BTREE | | | | user | 1 | age_name_email | 3 | email | A | 2435287 | NULL | NULL | | BTREE | | | | user | 1 | name_age_email | 1 | name | A | 1623524 | NULL | NULL | | BTREE | | | | user | 1 | name_age_email | 2 | age | A | 1623524 | NULL | NULL | | BTREE | | | | user | 1 | name_age_email | 3 | email | A | 2435287 | NULL | NULL | | BTREE | | | +-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 7 rows in set (0.01 sec)
MySQL [test_db_for_index]> desc select age,name,email from user where age>1 order by name limit 1; +----+-------------+-------+-------+----------------+----------------+---------+------+------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+----------------+----------------+---------+------+------+--------------------------+ | 1 | SIMPLE | user | index | age_name_email | name_age_email | 1535 | NULL | 2 | Using where; Using index | +----+-------------+-------+-------+----------------+----------------+---------+------+------+--------------------------+ 1 row in set (0.01 sec)
MySQL [test_db_for_index]> desc select age,name,email from user where age>50 order by name limit 1; +----+-------------+-------+-------+----------------+----------------+---------+------+------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+----------------+----------------+---------+------+------+--------------------------+ | 1 | SIMPLE | user | index | age_name_email | name_age_email | 1535 | NULL | 2 | Using where; Using index | +----+-------------+-------+-------+----------------+----------------+---------+------+------+--------------------------+ 1 row in set (0.01 sec)
MySQL [test_db_for_index]> desc select age,name,email from user where age>70 order by name limit 1; +----+-------------+-------+-------+----------------+----------------+---------+------+------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+----------------+----------------+---------+------+------+--------------------------+ | 1 | SIMPLE | user | index | age_name_email | name_age_email | 1535 | NULL | 3 | Using where; Using index | +----+-------------+-------+-------+----------------+----------------+---------+------+------+--------------------------+
MySQL [test_db_for_index]> desc select age,name,email from user where age>80 order by name limit 1; +----+-------------+-------+-------+----------------+----------------+---------+------+------+------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+----------------+----------------+---------+------+------+------------------------------------------+ | 1 | SIMPLE | user | range | age_name_email | age_name_email | 1 | NULL | 1 | Using where; Using index; Using filesort | +----+-------------+-------+-------+----------------+----------------+---------+------+------+------------------------------------------+ 1 row in set (0.02 sec)