Some tips

[Diegow]

Hi guys!

Well, I'm from brazil and my english is not so good, please sorry my errors!
So...
I am responsible for a forum and did the installation of this MOD Statistics.
But, in some pages of stats.php, occur some errors because, or MySQL "bugs", or query poorly drafted.

Example, this is original query (isn't work in my forum):

Code: Select all

	$sql = 'SELECT COUNT(DISTINCT p.poster_id) AS count, f.forum_id AS f_id, f.forum_name AS f_name 
				FROM ' . POSTS_TABLE . ' p, ' . FORUMS_TABLE . ' f 
				WHERE p.forum_id = f.forum_id 
					AND p.post_approved = 1' . $forum_sql . '
					AND f.forum_type = ' . FORUM_POST . '
				GROUP BY f.forum_id, f.forum_name
				ORDER BY count ' . $order;

this is a modified query(works, and is so fast)

Code: Select all

$sql = 'SELECT COUNT(DISTINCT p.poster_id) AS count, z.forum_id AS f_id, z.forum_name AS f_name 
				FROM (SELECT * FROM ' . POSTS_TABLE . ' WHERE post_approved = 1 ) AS p INNER JOIN 
					(SELECT forum_id, forum_name, forum_type FROM ' . FORUMS_TABLE . ' f WHERE f.forum_type = ' . FORUM_POST . '  ' . $forum_sql . '  )
					 z   ON p.forum_id = z.forum_id 				 
				GROUP BY z.forum_id, z.forum_name
				ORDER BY count ' . $order;

Looking further querys in the file functions.php, I saw that the union of tables is not made ​​using "INNER JOIN" (more fast), it's used "FROM table1, table2 WHERE table1.id = table2.id".

So, I'm telling this for in the future releases of this mod, queries can be more optimized for large forums

Other examples

before:

Code: Select all

$sql = 'SELECT f.forum_id AS f_id, f.forum_name AS f_name, (t.topic_replies_real + 1) AS count, t.topic_id AS t_id, t.topic_title AS t_title 
						FROM ' . FORUMS_TABLE . ' f, ' . TOPICS_TABLE . ' t 						
						WHERE t.forum_id = f.forum_id
							AND t.topic_approved = 1' . $forum_sql . '
							AND t.topic_status <> ' . ITEM_MOVED . '
						GROUP BY t.topic_id, f.forum_name, f.forum_id, t.topic_title, (t.topic_replies_real + 1)
						ORDER BY count ' . $order;		

after(more fast)

Code: Select all

$sql = 'SELECT f.forum_id AS f_id, f.forum_name AS f_name, (t.topic_replies_real + 1) AS count, t.topic_id AS t_id, t.topic_title AS t_title 
						FROM ' . FORUMS_TABLE . ' f INNER JOIN ' . TOPICS_TABLE . ' t ON t.forum_id = f.forum_id

						WHERE t.topic_approved = 1' . $forum_sql . '
							AND t.topic_status <> ' . ITEM_MOVED . '
						GROUP BY t.topic_id, f.forum_name, f.forum_id, t.topic_title, (t.topic_replies_real + 1)
						ORDER BY count ' . $order;		

before:

Code: Select all

	$sql = 'SELECT f.forum_id AS f_id, f.forum_name AS f_name, t.topic_views AS count, t.topic_id AS t_id, t.topic_title AS t_title 
						FROM ' . FORUMS_TABLE . ' f, ' . TOPICS_TABLE . ' t 						
						WHERE t.forum_id = f.forum_id
							AND t.topic_approved = 1' . $forum_sql . '	
							AND t.topic_status <> ' . ITEM_MOVED . '							
						GROUP BY t.topic_id, f.forum_name, f.forum_id, t.topic_title, t.topic_views					
						ORDER BY count ' . $order;		

after

Code: Select all

	$sql = 'SELECT f.forum_id AS f_id, f.forum_name AS f_name, t.topic_views AS count, t.topic_id AS t_id, t.topic_title AS t_title 
						FROM ' . FORUMS_TABLE . ' f INNER JOIN ' . TOPICS_TABLE . ' t 	 ON	 t.forum_id = f.forum_id

							WHERE t.topic_approved = 1' . $forum_sql . '	
							AND t.topic_status <> ' . ITEM_MOVED . '							
						GROUP BY t.topic_id, f.forum_name, f.forum_id, t.topic_title, t.topic_views					
						ORDER BY count ' . $order;		

I'm not perfect and this my suggestions of queries is not more efficiently than possible, but is better in my forum.

See ya!

[Marc]

Thanks for the tips. I'm aware of the shortcomings of phpBB Statistics 1.0.x and I'll try to improve the performance with SQL queries significantly in the next major version.