Geek Answers Handbook

How to improve MySQL query performance with frequent insertion?

I have approx. 200K rows in the tb_post table, and every 5 minutes it has approx. 10 new inserts.

I use the following query to retrieve strings -

 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 takes more than 10 seconds to sort all rows in a sorted way.

The following is an EXPLAIN request report:

  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

Multiple Inputs:

  • tb_post.RATING - type Float
  • There is an index on tb_post.USER_ID

Can someone offer me some guidance on how I can optimize this query and improve its reading performance?

PS: I am new to database scaling issues. Therefore, any suggestions would be useful for this request.

+4
source share
3 answers

You need an index for tb_post that covers both the ORDER BY clause and the WHERE clause.

 CREATE INDEX idx2 on tb_post (rating,user_id)

=> EXPLAIN SELECT output ... 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"; ""
+4
source

You can try indexing 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

If you are trying to aggregate data from different tables, you can also check what type of connection ( http://en.wikipedia.org/wiki/Join_(SQL) ) you want. Some of them are better than others, depending on what Do you want to.

+3
source

What happens if you release ORDER BY, will this affect performance? If this has a big effect, then it might be worth considering tb_post.RATING indexing.

Charles

+1
source

More articles:


All Articles