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
tb_post.RATINGis Float type
- There is index on
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.
You need an index for tb_post that covers both the ORDER BY and WHERE clause.
=> output of EXPLAIN SELECT ...ORDER BY tb_post.RATING DESC LIMIT 30