How to improve performance of MySQL query with frequent insert rate?

Question

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.

Sorry for the confusion. I corrected my question. I do want to index, and USER_ID is already indexed, but it's still not helping. The question is - shall I index other columns as well, and if yes, which ones? — Aditya
Maybee you should consider using JOIN operation and beware of the order you join your tables - smaller first. But it depends on a database you use.. more info stackoverflow.com/questions/173726/… — Martin Lazar

VolkerK VolkerK · Accepted Answer · 2009-05-28T09:18:07

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

CREATE INDEX idx2 on tb_post (rating,user_id)

=> output of 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";""

How to improve performance of MySQL query with frequent insert rate?

3 Answers