[SOLVED] Major performance problem

[walter8111]

Hi

 

I've a BIG performance problem on a simple query.

 

Server version: 5.0.41

 

SQL Statement:

SELECT fp.*, ft.first_post_id, ft.last_post_id
FROM forum_posts AS fp
    LEFT JOIN forum_topics AS ft ON ( fp.post_id = ft.last_post_id OR fp.post_id = ft.last_post_id ) 

 

 

Table Structures:

forum_topics

CREATE TABLE `forum_topics` (
         `topic_id` mediumint( NOT NULL auto_increment,
         `forum_id` mediumint( NOT NULL default '0',
         `status` tinyint(4) NOT NULL default '0',
         `type` tinyint(4) NOT NULL default '1',
         `title` varchar(50) NOT NULL default '',
         `first_post_id` mediumint( NOT NULL default '0',
         `last_post_id` mediumint( NOT NULL default '0',
         `moved_id` int(10) NOT NULL default '0',
         `last_user_id` int(10) NOT NULL default '0',
         `last_action_time` int(11) NOT NULL default '0',
         `replies` mediumint( NOT NULL default '0',
         `ratings` int(10) NOT NULL default '0',
         `tot_rating` int(10) NOT NULL default '0',
         `artist_id` int(10) NOT NULL default '0',
         `object_id` int(10) NOT NULL default '0',
         PRIMARY KEY  (`topic_id`)) ENGINE=MyISAM AUTO_INCREMENT=2062 DEFAULT CHARSET=latin1
  

 

Primary Key on topic_id

 

forum_posts

CREATE TABLE `forum_posts` (
         `post_id` mediumint( NOT NULL auto_increment,
         `topic_id` mediumint( NOT NULL default '0',
         `submitter_id` mediumint( NOT NULL default '0',
         `submitted_date` int(11) NOT NULL default '0',
         `updater_id` mediumint( NOT NULL default '0',
         `update_date` int(11) NOT NULL default '0',
         `name` varchar(50) NOT NULL default '',
         `submitter_ip` varchar( NOT NULL default '',
         PRIMARY KEY  (`post_id`)) ENGINE=MyISAM AUTO_INCREMENT=14089 DEFAULT CHARSET=latin1

 

Primary key on post_id

 

Explain query returns

mysql> explain select fp.*, ft.* from wal_forum_posts as fp left join wal_forum_

topics as ft on ( fp.post_id = ft.last_post_id or fp.post_id = ft.last_post_id);

 

+----+-------------+-------+------+---------------+------+---------+------+-------+-------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows  | Extra |
+----+-------------+-------+------+---------------+------+---------+------+-------+-------+
|  1 | SIMPLE      | fp    | ALL  | NULL          | NULL | NULL    | NULL | 13805 |       |
|  1 | SIMPLE      | ft    | ALL  | NULL          | NULL | NULL    | NULL |  2000 |       |
+----+-------------+-------+------+---------------+------+---------+------+-------+-------+

 

If I run this query in PHPMyAdmin the mysql-nt service goes up to 50% CPU power and after ages it timeouts. If I run this command in the MySQL Console, it does return the results: "13833 rows in set (1 min 54.22 sec)" (wich is way too long)

 

Although the tables themselves are not that big: wal_forum_topics has 2 000 records and wal_forum_posts has 13 000 records. If I run status check on my database in the mysql console during the query I receive this output:

"Threads: 2  Questions: 1101  Slow queries: 2  Opens: 111  Flush tables: 1  Open tables: 87  Queries per second avg: 1.117"

 

This query has been working fine for over a year now. What could be the cause?  And how could I resolve it? I'd think the indexes could be the problem, but I don't see how I could improve them.

 

And now, while I'm re-reading this post I notice the big flaw: it returns 13000 rows while it only should return 2000 rows! But I don't see the flaw in my query. It should return all info about all topics and their last- or firstpost.

 

All help is greatly appreciated!

 

 

 

[walter8111]

Okay I just found the problem, ofcourse it returns 13000 rows if you select the posts-table with their topics. But the full query actually was this:

 

SELECT f.*, ft.topic_id, ft.title, ft.first_post_id, ft.last_user_id, ft.last_action_time, fp.name AS post_username, u.username
		FROM ( ( ( forums AS f
			LEFT JOIN forum_posts AS fp ON f.last_post_id = fp.post_id )
			LEFT JOIN forum_topics AS ft ON ( fp.post_id = ft.last_post_id OR fp.post_Id = ft.first_post_id ) )
			LEFT JOIN users AS u ON ft.last_user_id = u.user_id )
		ORDER BY f.category_id ASC, f.`order` ASC

 

so it should return: all forums, their last post info and the topic information related to the last post (and the name of the member who submitted the post). Somehow this query makes MySQL slower than a sleepwalking snail.

[fenway]

Could you post the EXPLAIN for the full query?  You don't have any indexes on your columns you're using in your joins.  Hence possibile keys = NULL, hence ALL, hence table scan, hence bad.

[walter8111]

I found it! I had to add a "WHERE f.category_id > 0 " to the query solved the problem.

 

But you are right about the indexes, I never gave much tought about them. All primary keys are indexed. But should I index the foreign keys too?

 

forum_topics.topic_id is the PK

forum_posts.topic_id is the FK, should I put an index on that? Or doesn't that matter?

[fenway]

If you don't have indexes, then your where clause didn't "solve" the problem, it masked it.  At the very least, you need to make sure that mysql can quickly search the column you're using in your join condition.

[walter8111]

okay I understand & I knew that, but -aside from the PK's on the ID-fields. What other indexes should I make? Must I make an index for FK  too?

 

thus, should I make an index on forum_topics.first_post_id and forum_topics.last_post_id ?

[fenway]

Probably... though in that case, because you have OR, it won't really use both (unless you have index_merge and v5)... also, why are these left joins and not inner joins?

 

Basically, you need to give the server an way to find the matching row in the next table.  You start with searching the entire forums table.  You take the last_post_id, and look it up in the forum_posts table on post_id -- that should use the PK index.  Then you need the two FKs in the topics table.  Then the user_id (again, the PK).  I'd like to see the current EXPLAIN for that 4 table query.

[walter8111]

QUERY

SELECT f.*, ft.topic_id, ft.title, ft.first_post_id, ft.last_user_id, ft.last_action_time, fp.name AS post_username, u.username
		FROM ( ( ( forums AS f
			LEFT JOIN forum_posts AS fp ON f.last_post_id = fp.post_id )
			LEFT JOIN forum_topics AS ft ON ( fp.post_id = ft.last_post_id OR fp.post_Id = ft.first_post_id ) )
			LEFT JOIN users AS u ON ft.last_user_id = u.user_id )
		ORDER BY f.category_id ASC, f.`order` ASC

 

EXPLAIN output:

id 	select_type 	table 	type 	possible_keys 	key 	key_len 	ref 	rows 	Extra
1 	SIMPLE 	f 	ALL 	NULL 	NULL 	NULL 	NULL 	12157 	Using temporary; Using filesort
1 	SIMPLE 	fp 	eq_ref 	PRIMARY 	PRIMARY 	4 	walv6-3.f.last_post_id 	1 	 
1 	SIMPLE 	ft 	ALL 	NULL 	NULL 	NULL 	NULL 	1996 	 
1 	SIMPLE 	u 	eq_ref 	PRIMARY 	PRIMARY 	4 	walv6-3.ft.last_user_id 	1 	 

 

 

these are left joins because the relation doesn't nesecarelly exists:

forums = all forums (duh)

forum_posts = all seperate post. But it is possible a forum has no posts in it. I only need the last post made in that forum, so I make a join on the forum.last_post_id forum_posts.post_id (PK)

 

IF there is a last_post in the forum, there also must be a topic since every post is a child of a topic. A topic has a first_post-id and -if at least one reply has been given- a last_post_id.

 

If the post was made by a logged in user, I also fetch the name from the users table. If it was made by an unregistered user, the given name is saved in the posts-table.

 

So this query returns me all forums, if applicable their last post, all topic info for that post and -if applicable- the username of the member who posted that post.

[fenway]

If there aren't any posts, fp.* will be null, so you won't match and ft records, and then no user records either... so it really should be an inner join.

Did you index any of the other columns? What version of mysql are you running?

[walter8111]

Indexes solved it for me! Thanks for pointing that out, I never took much interest in it (aside Primary keys and a few indexes on much-selected-data-in-larger-tables). My biggest, most sluggisch queryies run like pigs-on-speed now