hi,all:
问题描述,我有一个表s,两个字段f,a 。创建了一个index(f,a)
当我使用where concat(f,a)= '0033';走索引但是,如果我让表中再加一个字段b
此时,我再使用where concat(f,a)= '0033';无论如何也不走索引了PS:where concat(f,cast(a,char))= '0033'; 试过也不走mysql> show create table s;
+-------+-----------------------------------------------------------------------
--------------------------------------------------------------------------------
+
| Table | Create Table|
+-------+-----------------------------------------------------------------------
--------------------------------------------------------------------------------
+
| s | CREATE TABLE `s` (
`f` varchar(10) NOT NULL DEFAULT '',
`a` int(4) DEFAULT NULL,
KEY `idx_f_a` (`f`,`a`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+-----------------------------------------------------------------------
--------------------------------------------------------------------------------
+
1 row in set (0.00 sec)mysql> explain select * from s where concat(f,a)= '0033';
+----+-------------+-------+-------+---------------+---------+---------+------+-
-----+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref |
rows | Extra |
+----+-------------+-------+-------+---------------+---------+---------+------+-
-----+--------------------------+
| 1 | SIMPLE | s | index | NULL | idx_f_a | 17 | NULL |
11 | Using where; Using index |
+----+-------------+-------+-------+---------------+---------+---------+------+-
-----+--------------------------+
1 row in set (0.00 sec)
问题描述,我有一个表s,两个字段f,a 。创建了一个index(f,a)
当我使用where concat(f,a)= '0033';走索引但是,如果我让表中再加一个字段b
此时,我再使用where concat(f,a)= '0033';无论如何也不走索引了PS:where concat(f,cast(a,char))= '0033'; 试过也不走mysql> show create table s;
+-------+-----------------------------------------------------------------------
--------------------------------------------------------------------------------
+
| Table | Create Table|
+-------+-----------------------------------------------------------------------
--------------------------------------------------------------------------------
+
| s | CREATE TABLE `s` (
`f` varchar(10) NOT NULL DEFAULT '',
`a` int(4) DEFAULT NULL,
KEY `idx_f_a` (`f`,`a`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+-----------------------------------------------------------------------
--------------------------------------------------------------------------------
+
1 row in set (0.00 sec)mysql> explain select * from s where concat(f,a)= '0033';
+----+-------------+-------+-------+---------------+---------+---------+------+-
-----+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref |
rows | Extra |
+----+-------------+-------+-------+---------------+---------+---------+------+-
-----+--------------------------+
| 1 | SIMPLE | s | index | NULL | idx_f_a | 17 | NULL |
11 | Using where; Using index |
+----+-------------+-------+-------+---------------+---------+---------+------+-
-----+--------------------------+
1 row in set (0.00 sec)
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货