Mysql - “选择喜欢”不使用索引

问题描述：

我一直在使用MySQL上的索引（5.5.24，WinXP），但我找不到为什么服务器在 LIKE $ c时没有使用一个索引的原因使用$ c>。

I have been playing around with indexes on MySQL (5.5.24, WinXP), but I can't find the reason of why the server is not using one index when a LIKE is used.

示例如下：

我创建了一个测试表：

create table testTable (
  id varchar(50) primary key,
  text1 varchar(50) not null,
  startDate varchar(50) not null
) ENGINE = innodb;

然后，我添加了一个索引到 startDate 。（请不要问为什么列是文本而不是日期时间..这只是一个简单的测试）：

Then, I added an index to startDate. (Please, do not ask why the column is a text and not a date time.. this is just a simple test):

create index jeje on testTable(startdate);
analyze table testTable;

之后，我添加了近200,000行，其中 startDate 有3行可能的价值观（每个人的三分之一出现...近70,000次）

After that, I added almost 200,000 rows of that where startDate had 3 possible values. (One third of appearences for each one..near 70,000 times)

所以，如果我运行这样的 EXPLAIN 命令：

So, if I run an EXPLAIN command like this:

explain select * from testTable use index (jeje) where startDate = 'aaaaaaaaa';

答案如下：

id = 1
select_type = SIMPLE
type = ref
possible_keys = jeje
key = jeje
rows = 88412
extra = Using where

因此，使用密钥，行数接近200,000 / 3所以一切都好。

So, the key is used, and the rows amount is near to 200,000/3 so all is ok.

如果我将查询更改为:(只是将'='转换为'LIKE'）：

The poblem is that if I change the query to: (just chaning '=' to 'LIKE'):

explain select * from testTable use index(jeje) where startDate LIKE 'aaaaaaaaa';

在这种情况下，答案是：

In this case, the answer is:

id = 1
select_type = SIMPLE
type = ALL
possible_keys = jeje
key = null
rows = 176824
extra = Using where

因此，索引现在没有被使用（key为null，和完整表附近的行......如类型=所有建议的那样。

So, the index is not being used now(key is null, and rows near to the full table..as the type=all suggests).

MySQL文档说LIKE DOES使用索引。

MySQL documentation says that LIKE DOES make use of indexes.

那么，我在这里看不到什么？问题出在哪里？

So, what am i not seeing here? Where is the problem?

感谢您的帮助。

答

根据 Ubik评论和数据更改，我发现：
索引 IS 在这些情况下使用：

Based on Ubik comment, and data changes, I found that: The Index IS used in these cases:

- explain select * from testTable force index jeje where startDate like 'aaaaaaadsfadsfadsfasafsafsasfsadsfa%';
- explain select * from testTable force index jeje where startDate like 'aaaaaaadsfadsfadsfasafsafsasfsadsfa%';
- explain select * from testTable force index jeje where startDate like 'aaa';

但是索引 NOT 正在使用当我使用这个查询时：

But the index is NOT being used when I use this query:

- explain select * from testTable force index jeje where startDate like 'aaaaaaaaa';

基于 startDate 列中所有值都相同的事实长度（9个字符），当我使用LIKE命令和9个字符常量使用查询时， PERHAPS MySQL因为一些性能算法而不喜欢使用原因，并转到表中。

Based on the fact that in startDate column all the values have the same length (9 characters), when I use a query using a LIKE command and a 9 characters constant, PERHAPS MySQL prefer to not use the reason because of some performance algorithm, and goes to the table.

我关心的是看我是否在原始测试中犯了某种错误，但现在我认为索引和测试是正确的，并且MySQL在某些情况下决定不使用索引...我会继续这个。

My concern was to see if I was making some kind of mistake on my original tests, but now I think that the index and tests are correct, and that MySQL in some cases decides to not use the index... and I will relay on this.

对我来说，这是一个封闭的任务。
如果有人想在线程中添加一些东西，欢迎你。

For me, this is a closed task. If somebody want to add something to the thread, you are welcome.

Mysql - “选择喜欢”不使用索引

相关推荐