I'm having approx. 200K rows in a table tb_post
, and every 5 minutes it has approx. 10 new inserts.
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.
Following is the report of EXPLAIN query:
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
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.