XF 1.4 Is this query fine or it needs more indexes?


Well-known member
Happens when viewing the report list, currently 4,500+ active reports.


The CPU usage of the query is minimal and the run time is less than 0.1 seconds so I guess it's fine but I'm just wondering about the "using filesort" part because I have heard that when possible we should get rid of using filesorts.



Well-known member
As far as I know, you're going to have a filesort anytime you have an ORDER BY clause in your SQL, but they are necessary if you want those results in a particular order.


XenForo developer
Staff member
You won't be missing any indexes. MySQL chose not to use it (likely because report_state is a better index).

I would again point out that 4500 open reports isn't the optimized use case. Code is written around how systems are expected to be used. If you veer way way off that course, you may be pushing it in ways it wasn't designed.


Well-known member
@Super120 Adding indexes isn't going to help.

The Report centre loads all open/assigned reports, and then all closed reports as of a time-window. It is then later processing in php which removes results which can't be seen.