1. 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?

Discussion in 'XenForo Questions and Support' started by imthebest, Jan 18, 2015.

  1. imthebest

    imthebest Formerly Super120

    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.
     
  2. SneakyDave

    SneakyDave 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.
     
  3. Luke F

    Luke F Well-Known Member

    Looks like your install is missing the key on last_modified_date?
     
  4. imthebest

    imthebest Formerly Super120

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

    Mike 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.
     
    Chris D and Amaury like this.
  6. Xon

    Xon 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.
     
  7. ANGEL OF FIRE

    ANGEL OF FIRE Member

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

Share This Page