hi all,

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,
rysolag

By the first glance, the query looks allright and there's not so many examined rows when running explain select.

I think it's because of your host (as you've concluded yourself).

The EXPLAIN says it's building 35000 unique rows, which is non-trivial for a web app. This is because the entries and comments are independent, so you wind up building needless combinations.

You can try making the mid index (memblog_id, entry_id/comment_id), which may allow it to work from the index without pulling the row itself. Seriously consider storing the counts in the memblog table, though. Normalization and performance don't always mix.

hiryuu - would you seriously consider storing the entry and comment counts in the memblog table? doesn't this go against relational database structure all together? it would be surprising to me if thing's could not be sped up without using this method.

memblog_id, entry_id and comment_id are already keyed up...

does using a sub query(within in the existing query) for either the entry count or the comment count seem like a suitable way to speed things up?

this way i wouldn't be joining on one of those multipliers...?

did you try tuning the my.cnf?

set up your members table to hold the number of entries, comments and latest post time. This way you don't have to join the two other tables. Also, it will save you having to use distinct, which is very costly in speed.

Originally posted by rysolag
hiryuu - would you seriously consider storing the entry and comment counts in the memblog table? doesn't this go against relational database structure all together?
Database theory focuses on consistency and maintainability. Performance often requires sacrificing that a bit to reduce the working set. Subqueries should help (since you're no longer trying to relate entries and comments, which are not related), but calculating on the fly will always be much slower than storing the static result.

what's the my.cnf?

my.cnf is the MySQL configuration file. It store various settings, and is where people look wehn they talk about tweaking MySQL performance.

-- HW