嗨,这个查询需要 100 毫秒,我认为它太多了?
SELECT
COUNT(
s0_.id
) AS sclr0,
s0_.id AS id1,
s0_.status AS status2,
...
s0_.public AS public22,
t1_.username AS username23,
t1_.username_canonical AS username_canonical24,
...
t1_.old_new AS old_new57,
t1_.image_name AS image_name58,
s0_.user_id AS user_id59
FROM
statuses s0_
INNER JOIN
users t1_ ON s0_.user_id = t1_.id
WHERE
s0_.time >= ? AND s0_.suggested_status = 1
GROUP BY
t1_.id
ORDER BY
sclr0 DESC
LIMIT
10
Parameters: [1376784000]
[Display runnable query]
Time: 108.53 ms [ - Explain query ]
以及解释:
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1_ index PRIMARY PRIMARY 4 12132 Using temporary; Using filesort
1 SIMPLE s0_ ref IDX_4BF01E11A76ED395 IDX_4BF01E11A76ED395 4 db.t1_.id 3 Using where
我做错了什么吗?
查询点是选择已发布状态标记为"建议"的所有用户,并按上个月每个用户的建议状态计数对用户进行排序。
尝试将索引添加到表中s0_.suggested_status 和 s0_.time。
ALTER TABLE `statuses` ADD INDEX `suggested_status` (`suggested_status`);
ALTER TABLE `statuses` ADD INDEX `time` (`time`);
http://dev.mysql.com/doc/refman/5.0/en/create-index.html
如何将索引添加到 MySQL 表?
您可以将索引添加到教义注释(或 yaml/xml)中:http://docs.doctrine-project.org/en/2.0.x/reference/annotations-reference.html#annref-index