Mysql版本-5.5.39
我有两张桌子BugsBugStatus
我想获取给定用户的OpenClosed错误计数。
我当前正在使用此查询

SELECT BugStatus.name,
    count(BugStatus.name) AS count
FROM bugs
INNER JOIN BugStatus ON bugs.status = bugstatus.id
WHERE bugs.assignee='irakam'
GROUP BY bugstatus.name;

现在假设我的Bugs表中有100000行。这个查询是否仍然有效,或者我应该如何修改它。我确实使用了Explain,但我仍然感到困惑。那么这个查询可以优化吗?
SQLFiddle链接-Click here

最佳答案

Select  bs.name,
        count(*) as count  -- simply count(*) unless you are avoiding nulls
    from  bugs
    inner join  BugStatus AS bs  ON bugs.status = bs.id
    where  bugs.assignee='irakam'
    group by  bs.name;

bugs:  INDEX(assignee)  -- since filtering occurs first

Index Cookbook

09-15 19:42