I have big table with 22 millions records. I want to execute next query:
我有2200万条记录的大桌子。我想执行下一个查询:
select auto_alerts from alerts_stat where endDate > "2012-12-01"
To improve performance I added BTREE index for endData field:
为了提高性能,我为endData字段添加了BTREE索引:
CREATE INDEX endDate_index USING BTREE ON alerts_stat(endDate)
After I start to analyze query execution plan:
在我开始分析查询执行计划之后:
When I want to get parameters from 15 to 7 days before now:
当我想要在15到7天之前获取参数时:
explain select alerts_sp from alerts_stat
where endDate between CURDATE() - 15 and CURDATE() - 7;
I got next execution plan to process 2,762,088 rows.
我有下一个执行计划来处理2,762,088行。
'1', 'SIMPLE', 'browser_plugin_alerts_stat', 'range', 'endDate_index', 'endDate_index', '4', NULL, '2762088', 'Using where'
When I increase interval by one day, i received:
当我将间隔增加一天时,我收到了:
explain select alerts_sp from alerts_stat
where endDate between CURDATE() - 15 and CURDATE() - 6;
EXPLAIN said MySQL plan to process all 22,923,126 rows.
EXPLAIN称MySQL计划处理所有22,923,126行。
'1', 'SIMPLE', 'browser_plugin_alerts_stat', 'ALL', 'endDate_index', NULL, NULL, NULL, '22932390', 'Using where'
For example select without any conditions in WHERE process 22,925,642.
例如,在WHERE过程22,925,642中选择没有任何条件。
May I improve execution plan? Maybe I have mistake somewhere, or is normal MySQL behaivior?
我可以改进执行计划吗?也许我在某个地方有错误,或者是正常的MySQL行为?
1 个解决方案
#1
3
When result set exceeds 8-9% of all rows, MySQL does full table scan. To me, it looks like that one day you add swings MySQL in full table scan direction. You could try forcing index to see if result is better.
当结果集超过所有行的8-9%时,MySQL会执行全表扫描。对我来说,有一天你会在全表扫描方向上添加摆动MySQL。您可以尝试强制索引以查看结果是否更好。
UPDATE:
From what I've read, MySQL query optimizer tends to choose wrong in borderline cases like this, so it could easily work better forcing an index. Otherwise, this is simple query, and I don't much more room for optimization.
从我所读到的,MySQL查询优化器倾向于在这样的边缘情况下选择错误,因此它可以更容易地更好地强制索引。否则,这是简单的查询,我没有更多的优化空间。
Maybe creating a Covering index on these two columns and forcing its use could give best results.
也许在这两列上创建覆盖索引并强制使用它可以提供最佳结果。
#1
3
When result set exceeds 8-9% of all rows, MySQL does full table scan. To me, it looks like that one day you add swings MySQL in full table scan direction. You could try forcing index to see if result is better.
当结果集超过所有行的8-9%时,MySQL会执行全表扫描。对我来说,有一天你会在全表扫描方向上添加摆动MySQL。您可以尝试强制索引以查看结果是否更好。
UPDATE:
From what I've read, MySQL query optimizer tends to choose wrong in borderline cases like this, so it could easily work better forcing an index. Otherwise, this is simple query, and I don't much more room for optimization.
从我所读到的,MySQL查询优化器倾向于在这样的边缘情况下选择错误,因此它可以更容易地更好地强制索引。否则,这是简单的查询,我没有更多的优化空间。
Maybe creating a Covering index on these two columns and forcing its use could give best results.
也许在这两列上创建覆盖索引并强制使用它可以提供最佳结果。