XF 2.1 How can I speed up this post finder query?

[HJW]

Hi,

I've got the query below to find all posts for a tag. It takes often a minute or just times out as it can be searching through hundreds of threads even though it's limited to a few results.

Will some indexes make it perform at a decent speed? Or is it a problem with my amateur coding?

		$threadidz = $db->fetchAll("SELECT th.thread_id FROM `xf_tag_content` as tc, xf_tag as t, xf_thread as th
where tc.content_type = 'thread'
and t.tag_id = tc.tag_id
and th.thread_id = tc.content_id
and t.tag_id =" . $tag['tag_id']);

		$threadidzz = $db->quote($threadidz);

		$postRepo = $this->getPostRepo();


				$postList = $postRepo->finder('XF:Post');
				$postList
					->where('reaction_score', '>=', 100)
					->whereSql("xf_post.thread_id IN ($threadidzz)")
					->setDefaultOrder('reaction_score', 'DESC');

 

[Lukas W.]

XenForo already has controllers that perform these lookups. I'd suggest looking into how they implement that.

 

[Xon]

First up;

• Use the join syntax, not the list all the tables in the from clause.
  • This avoids unexpected cartesian cross joins, where every row from multiple tables are pulled up.
• Avoid extra joins you don't need
  • Touching the xf_thread table is unneeded.
• Use parametrized queries. Always.

Selecting thread id's can be done much much easier;

SELECT distinct  tc.content_id
FROM `xf_tag_content` as tc
join xf_tag as t on  t.tag_id = tc.tag_id
where tc.content_type = 'thread' and t.tag_id = ?

This ensure you have a unique list of thread ids, avoids touching the thread table (you don't need to), and uses parameterized queries.

But your major performance issue is there is no index on the xf_post.reaction_score column and the thread_id column. This will result in some really horrible query performance to find the selection of posts you want. Especially as it is returning all posts without any pagination or limits!

Finally; you can just pass the list of $threadIds to where() and XF will format it properly;

               $postList = $postRepo->finder('XF:Post');
                $postList
                    ->where('reaction_score', '>=', 100)
                    ->where('thread_id', $threadids)
                    ->setDefaultOrder('reaction_score', 'DESC');

You'll probably want a limit clause, add a visibility check (where('message_state', 'visible')).

 

[HJW]

@Xon thanks so much that's so useful, I knew I would be doing silly things!

Is it an easy fix to just add an index for reaction_score and thread_id like this?



I did try a limitbypage and limit but I must have done something wrong as it didn't seem to reduce the dataset, if implemented correctly would it be a big performance improvement?

 

[Xon]

You will need to experiment to see if an index on reaction_score or reaction_score, thread_id or thread_id, reaction_score work best. (Column order can matter!)

I did try a limitbypage and limit but I must have done something wrong as it didn't seem to reduce the dataset, if implemented correctly would it be a big performance improvement?

Yes, but it is dependent on an index being used to correctly limit the results without sorting first.

 

[HJW]

Thanks so much @Xon

 

[Kirby]

I could be wrong, but I'd expect an index on reaction_score, thread_id to yield better results than thread_id, reaction_score if the query is always for values >= 100 as that seems to be a pretty high number (to me)