I'm struggling with the optimization of a MySQL query.
我正在努力优化MySQL查询。
select *
from course as t1
left join semester as t3 on t1.semester=t3.id
where t1.visibleFrom <= '1364621522'
and '1364621522' <= t1.visibleTo
order by t3.begin desc, t1.name;
This is reported by EXPLAIN to use an ALL-query on the course table. The table has several different indexes (visibleFrom, visibleTo, combination of both, name, semester). name is a varchar column, begin, visibleFrom and visibleTo are integers that can also be null.
EXPLAIN报告在课程表上使用ALL查询。该表有几个不同的索引(visibleFrom,visibleTo,两者的组合,名称,学期)。 name是varchar列,begin,visibleFrom和visibleTo是也可以为null的整数。
If I leave out the join with t3and force the use of the index name it works somehow.
如果我省略了与t3的连接并强制使用索引名称,它会以某种方式工作。
Any ideas why this query does not make use of indexes?
任何想法为什么这个查询不使用索引?
1 个解决方案
#1
1
I think the important index for the range restriction in the WHERE is the index on the two columns visibleFrom and visibleTo, i.e.
我认为WHERE中范围限制的重要索引是两列visibleFrom和visibleTo的索引,即
ALTER TABLE `course` ADD INDEX ( `visibleFrom` , `visibleTo` );
If MySQL doesn't use it in your query, my guess would be that the course table has less than ~ 100 rows. Therefore, the MySQL query optimizer decides not to use the index because of the overhead. As soon as the course table contains several hundreds of rows, the index should be used.
如果MySQL没有在你的查询中使用它,我的猜测是课程表少于~100行。因此,MySQL查询优化器决定不使用索引,因为开销。一旦课程表包含数百行,就应该使用索引。
#1
1
I think the important index for the range restriction in the WHERE is the index on the two columns visibleFrom and visibleTo, i.e.
我认为WHERE中范围限制的重要索引是两列visibleFrom和visibleTo的索引,即
ALTER TABLE `course` ADD INDEX ( `visibleFrom` , `visibleTo` );
If MySQL doesn't use it in your query, my guess would be that the course table has less than ~ 100 rows. Therefore, the MySQL query optimizer decides not to use the index because of the overhead. As soon as the course table contains several hundreds of rows, the index should be used.
如果MySQL没有在你的查询中使用它,我的猜测是课程表少于~100行。因此,MySQL查询优化器决定不使用索引,因为开销。一旦课程表包含数百行,就应该使用索引。