Forum Moderators: coopster
i run a blog site where the users can create and maintain their own blog. the home page lists the blogs along with information such as total number of entries, total number of comments and last time an entry was made.
i couple months back this list was running slow. little did i know, none of the fields had keys/indexes. so i created a bunch of keys and it sped up this list query a lot and i was pleased with its speed.
but now for some reason it is becoming slow. the only thing i can think of is that the query is not scaling well as more and more data enters the database. but this does not make sense to me because there have only been about 500 - 1200 new rows added to the database which is nothing. right?
here's the query:
SELECT m.memblog_id, m.blog_name, m.member_name, m.blog_views, m.avatar_path, COUNT(DISTINCT(e.entry_id)) AS blog_entries, COUNT(DISTINCT(c.comment_id)) AS blog_comments, MAX(e.entry_time) AS blog_last
FROM memblogs m LEFT JOIN entries e ON m.memblog_id = e.memblog_id LEFT JOIN comments c ON m.memblog_id = c.memblog_id
WHERE m.member_status IN (1, 2)
GROUP BY m.memblog_id
ORDER BY blog_last DESC, m.blog_views DESC, m.blog_name
LIMIT 0, 20;
so i'm wondering if anyone sees anything wrong with this right off the bat. everything has a key/index so don't tell me to do that.
here's the explain statement for the above query:
table ¦ type ¦ possible_keys ¦ key ¦ key_len ¦ ref rows ¦Extra
-----------------------------------------------------
m ¦ range ¦ mStatus ¦ mStatus ¦ 1 ¦ NULL ¦ 298 ¦ Using where; Using temporary; Using filesort
e ¦ ref ¦ mid ¦ mid ¦ 3 ¦ m.memblog_id 8
c ¦ ref ¦ mid ¦ mid ¦ 3 ¦ m.memblog_id 15
so if anyone has any feedback that would be great. i'm starting to think that it's my server. this site is currently being run on a shared server(reseller accout), so who knows what's going on there.
thanks guys
Do you have a lot of deleted rows in your tables?
Yeah, this sounds like a fragmentation issue - as rows are inserted/deleted over time the indexes on the table become fragmented. This means the database has to do more IO to run the same query (more pages have to be read into memory). Try running the following command on your tables:
alter table table_name type=InnoDB
This command should be run as a housekeeping process every once in a while along with sql_updatestat which updates table statistics (thus enabling the optimiser to choose the most efficient query plan)
