XF 2.2 Simple Query very slow

[MrFreeze]

I have this simple query but it is totally lame. What can I do to get more performance out of it?

 

[Lukas W.]

Your database is picking the wrong indexes to run the query. If you add index hints to the query, it should sort itself out.

 

[Xon]

I'ld recommend rewriting that query from a left join to join, and moving and reci.user_id = ? into the where clause. Sometimes MySQL picks wonky stuff if your join condition isn't 100% what it expects.

How many conversations does that user have? Because the explain is hitting at ~175k or so.