SQL Query optimization

[takufner]

Hello all.

I've written an extension for personal use that counts the total solved topics and the user solved topics with the following two php functions:

Code: Select all

	public function qtd_solved($user_id)
	{
		$sql = 'SELECT COUNT(p.post_id) AS topics_solved
			FROM ' . POSTS_TABLE . ' p
			WHERE p.poster_id = ' . (int) $user_id . ' AND 
			p.post_id IN (SELECT t.topic_solved FROM phpbb_topics AS t)';
		$result = $this->db->sql_query($sql, 10); // results cached for 10 seconds
		$solved_rating = $this->db->sql_fetchrow($result);
		$this->db->sql_freeresult($result);
		return $solved_rating['topics_solved'];
	}
	
	public function total_solved_ttb()
	{
		$sql = 'SELECT COUNT(DISTINCT t.topic_solved) AS total_solved
			FROM ' . TOPICS_TABLE . ' AS t';
		$result = $this->db->sql_query($sql, 30); // results cached for 30 seconds
		$total_solved = $this->db->sql_fetchrow($result)['total_solved'];
		$this->db->sql_freeresult($result);
		return $total_solved;
	}

But both SELECT queries are taking too much time to execute (160 ms the first and 80 ms the second).

Since I run this querie in every viewtopic load, it is crucial to have it optimized.

Is there some SQL guru that could help me optimize these functions?

Obs.: Topics solved are from the extension Topic Solved.

Kind regards,

[rxu]

Not an SQL guru here, but I'd try something like

Code: Select all

		$sql = 'SELECT COUNT(p.post_id) AS topics_solved
			FROM ' . POSTS_TABLE . ' p, ' . TOPICS_TABLE . ' t
			WHERE p.poster_id = ' . (int) $user_id . '
				AND p.post_id = t.topic_solved
				AND t.topic_solved <> 0';

		$sql = 'SELECT COUNT(DISTINCT topic_solved) AS total_solved
			FROM ' . TOPICS_TABLE . '
			WHERE topic_solved <> 0';

[takufner]

Thanks for your reply, rxu.
I'll try to run benchmarks with this code this weekend
The first one looks promissing