Shulou(Shulou.com)05/31 Report--

This article is to share with you about how to test the index of OR in Percona MySQL 5.6WHERE conditions. The editor thinks it is very practical, so I share it with you. I hope you can get something after reading this article. Without saying much, let's take a look at it.

Create an index on the two columns of the test table.

Mysql > select * from test

+-+ +

| | id | name |

+-+ +

| | 10 | neo |

| | 20 | John |

| | 30 | Lucy |

| | 40 | Larry |

| | 50 | Lilly |

+-+ +

5 rows in set (0.00 sec)

Mysql > show keys from test

Empty set (0.01sec)

Mysql > create index idx_test_id on test (id)

Query OK, 0 rows affected (0.34 sec)

Records: 0 Duplicates: 0 Warnings: 0

Mysql > create index idx_test_name on test (name)

Query OK, 0 rows affected (0.72 sec)

Records: 0 Duplicates: 0 Warnings: 0

Mysql > show keys from test

+-+ -+

| | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |

+-+ -+

| | test | 1 | idx_test_id | 1 | id | A | 5 | NULL | NULL | YES | BTREE |

| | test | 1 | idx_test_name | 1 | name | A | 5 | NULL | NULL | YES | BTREE |

+-+ -+

2 rows in set (0.02 sec)

These two columns are covered in the OR condition, and these two indexes are used.

Mysql > explain select * from test where id=10

+-- +

| | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+-- +

| | 1 | SIMPLE | test | ref | idx_test_id | idx_test_id | 5 | const | 1 | NULL |

+-- +

1 row in set (0.00 sec)

Mysql > explain select * from test where name='Lucy'

+-+-

| | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+-+-

| | 1 | SIMPLE | test | ref | idx_test_name | idx_test_name | 18 | const | 1 | Using index condition |

+-+-

1 row in set (0.00 sec)

Mysql > explain select * from test where id=10 or name='Lucy'

+-+-- +-+

| | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+-+-- +-+

| | 1 | SIMPLE | test | ALL | idx_test_id,idx_test_name | NULL | NULL | NULL | 5 | Using where |

+-+-- +-+

1 row in set (0.00 sec)

Mysql > explain select * from test where id=20 or name='Lucy'

+-+-- +-+

| | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+-+-- +-+

| | 1 | SIMPLE | test | ALL | idx_test_id,idx_test_name | NULL | NULL | NULL | 5 | Using where |

+-+-- +-+

1 row in set (0.00 sec)

Mysql > explain select * from test where id=50 or name='neo'

+-+-- +-+

| | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+-+-- +-+

| | 1 | SIMPLE | test | ALL | idx_test_id,idx_test_name | NULL | NULL | NULL | 5 | Using where |

+-+-- +-+

1 row in set (0.00 sec)

Delete both indexes and rebuild an index that will be used in the OR condition.

Mysql > drop index idx_test_id

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near''at line 1

Mysql > drop index idx_test_id on test

Query OK, 0 rows affected (0.33 sec)

Records: 0 Duplicates: 0 Warnings: 0

Mysql > drop index idx_test_name on test

Query OK, 0 rows affected (0.02 sec)

Records: 0 Duplicates: 0 Warnings: 0

Mysql > create index idx_test_id on test (id)

Query OK, 0 rows affected (0.09 sec)

Records: 0 Duplicates: 0 Warnings: 0

Mysql > explain select * from test where id=50 or name='neo'

+-- +

| | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+-- +

| | 1 | SIMPLE | test | ALL | idx_test_id | NULL | NULL | NULL | 5 | Using where |

+-- +

1 row in set (0.00 sec)

Mysql > explain select * from test where name='neo'

+-- +

| | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+-- +

| | 1 | SIMPLE | test | ALL | NULL | NULL | NULL | NULL | 5 | Using where |

+-- +

1 row in set (0.00 sec)

Mysql > explain select * from test where name='Lucy'

+-- +

| | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+-- +

| | 1 | SIMPLE | test | ALL | NULL | NULL | NULL | NULL | 5 | Using where |

+-- +

1 row in set (0.00 sec)

Delete the index in the table and create a federated index containing the two columns, which is used in the OR condition. It is found that the application rules of federated indexes in MySQL are different from those in Oracle. There is only one column of WHERE conditions in a single query condition. For example, if the fields creating the federated index are An and B, the query on B will also use the federated index.

Mysql > drop index idx_test_name on test

ERROR 1091 (42000): Can't DROP 'idx_test_name'; check that column/key exists

Mysql > drop index idx_test_id on test

Query OK, 0 rows affected (0.09 sec)

Records: 0 Duplicates: 0 Warnings: 0

Mysql > create index idx_test_id_name on test (id,name)

Query OK, 0 rows affected (0.21 sec)

Records: 0 Duplicates: 0 Warnings: 0

Mysql > show keys from test

+ -- +

| | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |

+ -- +

| | test | 1 | idx_test_id_name | 1 | id | A | 5 | NULL | NULL | YES | BTREE |

| | test | 1 | idx_test_id_name | 2 | name | A | 5 | NULL | NULL | YES | BTREE |

+ -- +

2 rows in set (0.00 sec)

Mysql > explain select * from test where name='Lucy'

+-+-

| | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+-+-

| | 1 | SIMPLE | test | index | NULL | idx_test_id_name | 23 | NULL | 5 | Using where; Using index |

+-+-

1 row in set (0.00 sec)

Mysql > explain select * from test where name='neo'

+-+-

| | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+-+-

| | 1 | SIMPLE | test | index | NULL | idx_test_id_name | 23 | NULL | 5 | Using where; Using index |

+-+-

1 row in set (0.00 sec)

Mysql > explain select * from test where id=10

+-- +

| | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+-- +

| | 1 | SIMPLE | test | ref | idx_test_id_name | idx_test_id_name | 5 | const | 1 | Using index |

+-- +

1 row in set (0.00 sec)

Mysql > explain select * from test where id=10 or name='Lucy'

+-+-

| | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+-+-

| | 1 | SIMPLE | test | index | idx_test_id_name | idx_test_id_name | 23 | NULL | 5 | Using where; Using index |

+-+-

1 row in set (0.00 sec)

The above is how to carry out the index test of OR in the Percona MySQL 5.6WHERE condition. The editor believes that there are some knowledge points that we may see or use in our daily work. I hope you can learn more from this article. For more details, please follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.