gpt4 book ai didi

mysql - MySQL查询多列索引表

转载 作者:行者123 更新时间:2023-11-30 22:00:13 25 4
gpt4 key购买 nike

我有一个包含 3 列的单个索引的 MySQL 表:colA、ColB、ColC

如果我这样查询:

SELECT * FROM MyTable WHERE colA='some value' AND colB = 'some other value'

是否使用了索引,或者我是否需要创建一个仅包含 colA 和 colB 的单独索引

最佳答案

DROP TABLE IF EXISTS my_table;


CREATE TABLE my_table
(id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
,dense INT NOT NULL
,sparse INT NOT NULL
, other INT NOT NULL
, INDEX(dense,sparse,other)
);

INSERT INTO my_table VALUES (1,1,1,1);

INSERT INTO my_table (dense,sparse,other) SELECT RAND()*10,RAND()*1000000,RAND()*1000 FROM my_table;

-- Repeat last command 10 times, or so...


EXPLAIN SELECT * FROM my_table WHERE dense = 9 AND sparse = 1000;
+----+-------------+----------+------+---------------+-------+---------+-------------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------+------+---------------+-------+---------+-------------+------+-------+
| 1 | SIMPLE | my_table | ref | dense | dense | 8 | const,const | 1 | |
+----+-------------+----------+------+---------------+-------+---------+-------------+------+-------+
1 row in set (0.00 sec)

EXPLAIN SELECT * FROM my_table WHERE sparse = 1000;
+----+-------------+----------+------+---------------+------+---------+------+-------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------+------+---------------+------+---------+------+-------+-------------+
| 1 | SIMPLE | my_table | ALL | NULL | NULL | NULL | NULL | 16384 | Using where |
+----+-------------+----------+------+---------------+------+---------+------+-------+-------------+
1 row in set (0.00 sec)

关于mysql - MySQL查询多列索引表,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/43632696/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com