mysql里是不是使用了函数,索引就不能用了? 就是sql语句里,如果使用了mysql的函数,索引就无效了?所以不推荐在sql里使用函数? 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 SQL语句、函数是什么?贴出来 我记得老版本应该是,现在应该是分函数的吧 用explain看一下。 是的。如果你的语句是 functionName(colname) 或者某些运算 则MYSQL无法使用基于 colName的索引。 col varchar(100), index columnwhere left(col,4) = 'aaa' 这种可以使用索引。 谢,left很少用,一般都like 'aaa%', #4楼应该都用不上索引了。求反例。col varchar(100), index columnwhere left(col,4) = 'aaa' 这种可以使用索引。====错误!这个函数也是不能用索引的。mysql> desc site_quans;+--------------+-------------+------+-----+------------+----------------+| Field | Type | Null | Key | Default | Extra |+--------------+-------------+------+-----+------------+----------------+| id | int(11) | NO | PRI | NULL | auto_increment || user_quan_id | varchar(9) | NO | MUL | | || site_quan_id | varchar(20) | NO | MUL | | || site_id | int(11) | NO | | 0 | || quan_value | int(11) | NO | | 0 | || password | varchar(30) | NO | | | || start_date | date | NO | | 1970-01-01 | || end_date | date | NO | | 1970-01-01 | || status | tinyint(4) | NO | | 0 | || quan_type | tinyint(4) | NO | | 0 | |+--------------+-------------+------+-----+------------+----------------+10 rows in set (0.00 sec)mysql> explain select * from site_quans where left(site_quan_id, 4) = 'aaa';+----+-------------+------------+------+---------------+------+---------+------+------+-------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+------------+------+---------------+------+---------+------+------+-------------+| 1 | SIMPLE | site_quans | ALL | NULL | NULL | NULL | NULL | 1 | Using where |+----+-------------+------------+------+---------------+------+---------+------+------+-------------+1 row in set (0.00 sec) SQL查询出来的统计怎么不对? 求一select写法 跪求sql语句 请教一个mysql的多条件查询问题 mysql配置文件优化 mysql 备份与恢复 问题。急急急急急 加急。100分来啊 我已安装好mysql和mysqlserver等,但是打入mysql时出现错误 救急,关于mysql不能正确启动的问题,请指教! 大神求助这个哪有错误啊,半小时没看出来。。。 mysql启动出错 Error 1005, Can't Create Table... my.ini文件设置日志文件后读文件出错
col varchar(100), index columnwhere left(col,4) = 'aaa' 这种可以使用索引。
谢,left很少用,一般都like 'aaa%',
错误!这个函数也是不能用索引的。mysql> desc site_quans;
+--------------+-------------+------+-----+------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-------------+------+-----+------------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| user_quan_id | varchar(9) | NO | MUL | | |
| site_quan_id | varchar(20) | NO | MUL | | |
| site_id | int(11) | NO | | 0 | |
| quan_value | int(11) | NO | | 0 | |
| password | varchar(30) | NO | | | |
| start_date | date | NO | | 1970-01-01 | |
| end_date | date | NO | | 1970-01-01 | |
| status | tinyint(4) | NO | | 0 | |
| quan_type | tinyint(4) | NO | | 0 | |
+--------------+-------------+------+-----+------------+----------------+
10 rows in set (0.00 sec)mysql> explain select * from site_quans where left(site_quan_id, 4) = 'aaa';
+----+-------------+------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | site_quans | ALL | NULL | NULL | NULL | NULL | 1 | Using where |
+----+-------------+------------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)