I'm having approx. 200K rows in a table tb_post
, and every 5 minutes it has approx. 10 new inserts.
我有约。表tb_post中有200K行,每5分钟就有一行。 10个新插件。
I'm using following query to fetch the rows -
我正在使用以下查询来获取行 -
SELECT tb_post.ID, tb_post.USER_ID, tb_post.TEXT, tb_post.RATING, tb_post.CREATED_AT,
tb_user.ID, tb_user.NAME
FROM tb_post, tb_user
WHERE tb_post.USER_ID=tb_user.ID
ORDER BY tb_post.RATING DESC
LIMIT 30
It's taking more than 10sec to fetch all the rows in sorted fashion.
以分类的方式获取所有行需要10秒以上。
Following is the report of EXPLAIN query:
以下是EXPLAIN查询的报告:
id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tb_user ALL PRIMARY NULL NULL NULL 20950 Using temporary; Using filesort 1 SIMPLE tb_post ref tb_post_FI_1 tb_post_FI_1 4 tb_user.id 4
Few inputs:
-
tb_post.RATING
is Float type - There is index on
tb_post.USER_ID
tb_post.RATING是Float类型
tb_post.USER_ID上有索引
Can anyone suggest me few pointers about how should I optimize this query and improve its read performance?
任何人都可以建议我如何优化此查询并提高其读取性能?
PS: I'm newbie in database scaling issues. So any kinds of suggestions will be useful specific to this query.
PS:我是数据库扩展问题的新手。因此,任何类型的建议都将特定于此查询。
3 个解决方案
#1
You need an index for tb_post that covers both the ORDER BY and WHERE clause.
您需要一个涵盖ORDER BY和WHERE子句的tb_post索引。
CREATE INDEX idx2 on tb_post (rating,user_id)
=> output of EXPLAIN SELECT ...ORDER BY tb_post.RATING DESC LIMIT 30
=> EXPLAIN SELECT的输出... ORDER BY tb_post.RATING DESC LIMIT 30
"id";"select_type";"table";"type";"possible_keys";"key";"key_len";"ref";"rows";"Extra" "1";"SIMPLE";"tb_post";"index";NULL;"idx2";"10";NULL;"352";"" "1";"SIMPLE";"tb_user";"eq_ref";"PRIMARY";"PRIMARY";"4";"test.tb_post.USER_ID";"1";""
#2
You could try to index tb_post.RATING
: MySQL can sometimes use indexes to optimize ORDER BY
clauses : http://dev.mysql.com/doc/refman/5.0/en/order-by-optimization.html
您可以尝试索引tb_post.RATING:MySQL有时可以使用索引来优化ORDER BY子句:http://dev.mysql.com/doc/refman/5.0/en/order-by-optimization.html
If you're trying to aggregate data from different tables, you could also check which type of join ( http://en.wikipedia.org/wiki/Join_(SQL) ) you want. Some are better than others, depending on what you want.
如果您尝试聚合来自不同表的数据,您还可以检查所需的连接类型(http://en.wikipedia.org/wiki/Join_(SQL))。有些比其他更好,取决于你想要什么。
#3
What happens if you take the ORDER BY off, does that have a performance impact? If that has a large effect then maybe consider indexing tb_post.RATING.
如果你关闭ORDER BY会发生什么,这会对性能产生影响吗?如果这有很大的影响,那么可以考虑索引tb_post.RATING。
Karl
#1
You need an index for tb_post that covers both the ORDER BY and WHERE clause.
您需要一个涵盖ORDER BY和WHERE子句的tb_post索引。
CREATE INDEX idx2 on tb_post (rating,user_id)
=> output of EXPLAIN SELECT ...ORDER BY tb_post.RATING DESC LIMIT 30
=> EXPLAIN SELECT的输出... ORDER BY tb_post.RATING DESC LIMIT 30
"id";"select_type";"table";"type";"possible_keys";"key";"key_len";"ref";"rows";"Extra" "1";"SIMPLE";"tb_post";"index";NULL;"idx2";"10";NULL;"352";"" "1";"SIMPLE";"tb_user";"eq_ref";"PRIMARY";"PRIMARY";"4";"test.tb_post.USER_ID";"1";""
#2
You could try to index tb_post.RATING
: MySQL can sometimes use indexes to optimize ORDER BY
clauses : http://dev.mysql.com/doc/refman/5.0/en/order-by-optimization.html
您可以尝试索引tb_post.RATING:MySQL有时可以使用索引来优化ORDER BY子句:http://dev.mysql.com/doc/refman/5.0/en/order-by-optimization.html
If you're trying to aggregate data from different tables, you could also check which type of join ( http://en.wikipedia.org/wiki/Join_(SQL) ) you want. Some are better than others, depending on what you want.
如果您尝试聚合来自不同表的数据,您还可以检查所需的连接类型(http://en.wikipedia.org/wiki/Join_(SQL))。有些比其他更好,取决于你想要什么。
#3
What happens if you take the ORDER BY off, does that have a performance impact? If that has a large effect then maybe consider indexing tb_post.RATING.
如果你关闭ORDER BY会发生什么,这会对性能产生影响吗?如果这有很大的影响,那么可以考虑索引tb_post.RATING。
Karl