• This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn more.

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

imthebest

Formerly Super120
#1
Happens when viewing the report list, currently 4,500+ active reports.

query.png

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.

Thanks.
 

SneakyDave

Well-known member
#2
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.
 

Mike

XenForo developer
Staff member
#5
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.
 

Xon

Well-known member
#6
@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.