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

[imthebest]

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.

Thanks.

 

[SneakyDave]

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.

 

[Luke F]

Looks like your install is missing the key on last_modified_date?

 

[imthebest]

@Luke Foreman are you sure? Hope to get a confirmation from the devs... maybe because it is an import from vBulletin 3.8?

 

[Mike]

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]

@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.

 

[ANGEL OF FIRE]

Were able to fix the error or not? We have the same problem.