MySQL 中的查询非常慢
我有一个这样的场景；
我有一个名为“tbl_gust_comb_archve_01nov11_beyond”的表，
索引键设置在这些字段“Gid、gip、siteid、 kw、kwtype、dt、gpage、日期”
还有
这个我的查询是：
SELECT SQL_CALC_FOUND_ROWS
gid, gip, siteid, kw, kwtype, dt, count(id) as vpage, sum(mapped) as mapped
FROM
tbl_gust_comb_archve_01nov11_beyond
WHERE
confirmation = 1
AND
dated BETWEEN '2012-01-31' AND '2012-01-31'
AND
siteid = 'bing'
GROUP BY gid
ORDER BY dt
DESC LIMIT 0,50
如果您将日期设置为一个范围，例如 '2012-01-31' AND '2012-02-01' 那么结果将需要 10-30 分钟以上的时间。
如果您有一个日期范围并删除“GROUP BY”，那么结果会快得多（大约 5 分钟）。尽管！删除GROUP BY后，5分钟也太多了......
表大小是“30mill记录和12Gig”。
谢谢！
I have a scenario like;
I have a table named "tbl_gust_comb_archve_01nov11_beyond"
Indexed keys are set on these fields "Gid, gip, siteid, kw, kwtype, dt, gpage, dated"
And
This is my query:
SELECT SQL_CALC_FOUND_ROWS
gid, gip, siteid, kw, kwtype, dt, count(id) as vpage, sum(mapped) as mapped
FROM
tbl_gust_comb_archve_01nov11_beyond
WHERE
confirmation = 1
AND
dated BETWEEN '2012-01-31' AND '2012-01-31'
AND
siteid = 'bing'
GROUP BY gid
ORDER BY dt
DESC LIMIT 0,50
If you make the date a RANGE such as '2012-01-31' AND '2012-02-01' then the result will take longer then 10-30 minutes.
If you have a date range and REMOVE the "GROUP BY" then the result will be much faster (about 5 minutes). Though! after removing GROUP BY, 5 minutes are also too much...
Table size is "30mill records and 12Gig".
Thanks!
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
您应该首先对查询执行
EXPLAIN，正如马克·贝克在他的评论中建议的那样。但可能在这些列上创建多列索引应该可以解决问题：
dt（这可能应该是第一个）confirmationdatedsiteidgid我不确定应该如何对
gid建立索引（在哪个位置等）。更多详细信息在这里，因此您可以自行决定解决方案：
CREATE INDEX语法，ORDER BY优化，GROUP BY优化，You should first do
EXPLAINon the query, as Mark Baker suggested within his comment.But probably creating a multi-column index on these columns should solve the problem:
dt(this probably should be the first)confirmationdatedsiteidgidI am not sure how the
gidshould be indexed (on which position, etc.).More details are here, so you can decide on the solution on your own:
CREATE INDEXSyntax,ORDER BYOptimization,GROUP BYOptimization,如果 siteid 变化不大，您可以尝试删除 siteid 上的索引。
如果你有30米尔。 record 和 1/3 with siteid == "bing"，那么你的查询将
这是非常合乎逻辑的，因为选择一个范围通常比选择一个简单值要长。如果siteid变化很大，您可以尝试在日期和日期上添加双重索引。 站点 ID。
对于确认字段，由于您在存档表中，也许您可以将那些未确认的移动到其他表中。如果您能够删除此检查，您还可以获得一些速度。
If siteid does not vary a lot, you can try to remove your index on siteid.
If you have 30mill. record and 1/3 with siteid == "bing", then your query will
It's quite logical, since selecting a range is, normally, longer than selecting a simple value. If siteid does vary a lot, you can try to add a dual index on both dated & siteid.
For confirmation field, since you are in a archive table, maybe you can move those who have not confirmed to an other table. You can also gain some speed if you are able to remove this check.