select xx from tablexx where type in (1,3) and last<current-interval 30 second;
select xx from tablexx where type=1;
If create index on (type,last),the first one won't use index.
如果在(type,last)上创建索引,则第一个不会使用索引。
If create index on (last,type),the second one won't use index.
如果在(last,type)上创建索引,则第二个不使用索引。
As for data type,which is can be seen from the example,type: int unsigned,last: datetime
对于数据类型,可以从示例中看到,键入:int unsigned,last:datetime
3 个解决方案
#1
In the first query, MySQL is going to look for an index on 'last' because it is an inequality. I would then expect it to have to iterate over all records with 'last
在第一个查询中,MySQL将在'last'上查找索引,因为它是一个不等式。我希望它必须用'last'迭代所有记录
I would expect you'd get just as good performance with two separate indexes, one on 'last' (for the first query) and one on 'type' (for the second query).
我希望你能用两个单独的索引获得同样好的表现,一个在'last'(对于第一个查询),一个在'type'(对于第二个查询)。
The 'EXPLAIN' command can be really helpful for analysing this stuff.
'EXPLAIN'命令对于分析这些东西真的很有帮助。
#2
The second query, having only type = 1 in the where clause, only needs and index on type, not on (type, last).
第二个查询在where子句中只有type = 1,只需要和索引类型,而不是on(type,last)。
MySQL should pick the most specific index for your query, so creating an index just covering type should be used for the second one, but not the first one.
MySQL应该为您的查询选择最具体的索引,因此创建一个仅覆盖类型的索引应该用于第二个,而不是第一个。
#3
You stated "If create index on (type,last),the first one won't use index." Are you sure about this? I was under the impression this is exactly the circumstance under which a covering index would execute. EDIT: Unless of course there's a selectivity problem with the data - if most records have type 1 or 3 then the optimizer wouldn't use the index (regardless of whether it was a basic or composite index).
你声明“如果创建索引(类型,最后),第一个将不使用索引。”你确定吗?我的印象是,这正是覆盖指数将要执行的情况。编辑:当然除非数据存在选择性问题 - 如果大多数记录具有类型1或3,则优化器将不使用索引(无论它是基本索引还是复合索引)。
#1
In the first query, MySQL is going to look for an index on 'last' because it is an inequality. I would then expect it to have to iterate over all records with 'last
在第一个查询中,MySQL将在'last'上查找索引,因为它是一个不等式。我希望它必须用'last'迭代所有记录
I would expect you'd get just as good performance with two separate indexes, one on 'last' (for the first query) and one on 'type' (for the second query).
我希望你能用两个单独的索引获得同样好的表现,一个在'last'(对于第一个查询),一个在'type'(对于第二个查询)。
The 'EXPLAIN' command can be really helpful for analysing this stuff.
'EXPLAIN'命令对于分析这些东西真的很有帮助。
#2
The second query, having only type = 1 in the where clause, only needs and index on type, not on (type, last).
第二个查询在where子句中只有type = 1,只需要和索引类型,而不是on(type,last)。
MySQL should pick the most specific index for your query, so creating an index just covering type should be used for the second one, but not the first one.
MySQL应该为您的查询选择最具体的索引,因此创建一个仅覆盖类型的索引应该用于第二个,而不是第一个。
#3
You stated "If create index on (type,last),the first one won't use index." Are you sure about this? I was under the impression this is exactly the circumstance under which a covering index would execute. EDIT: Unless of course there's a selectivity problem with the data - if most records have type 1 or 3 then the optimizer wouldn't use the index (regardless of whether it was a basic or composite index).
你声明“如果创建索引(类型,最后),第一个将不使用索引。”你确定吗?我的印象是,这正是覆盖指数将要执行的情况。编辑:当然除非数据存在选择性问题 - 如果大多数记录具有类型1或3,则优化器将不使用索引(无论它是基本索引还是复合索引)。