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

XF 1.2 Tackling queries with some missing indexes

Discussion in 'Troubleshooting and Problems' started by Stuart Wright, Nov 17, 2013.

  1. Stuart Wright

    Stuart Wright Well-Known Member

    The following have been reported by my host Tim at Nimbus as slow queries.
    I've included these here because I believe they are probably core Xenforo queries.
    Can anything be done to speed them up please?

    I added indexes to the site_title and addon_id columns in the hope that these would help. (My understanding is that any columns being used in matches or sorts in queries should be considered for having an index on them)

    There are lots more, but I wanted to see whether this thread is welcome or not, first.
  2. Chris D

    Chris D XenForo Developer Staff Member

    Unless I'm misunderstanding the output of the log, I can't see that these are slow queries at all.

    The first one seems to have executed in 0.003 seconds.
    HWS and Liam W like this.
  3. Mike

    Mike XenForo Developer Staff Member

    Indeed, the first 2 don't seem to be slow. The last one looks slightly slow (0.1s), but it's not a data issue - if it's slow, it's slow because of a knock on effect or the data needing to be read from the disk. It only examined 12 rows, so indexing is going to do very little.
  4. Stuart Wright

    Stuart Wright Well-Known Member

    Ok, Tim has corrected me. They are queries with some missing indexes and I have edited the thread title to reflect that.

    Presumably in a lot of cases, adding indexes will improve query time, even if it's only by a small amount. It all adds up, right?
    I have been reading up on this.
    If there is an index composed of two columns, maybe to create a unique combination for a primary key, isn't it still worth having separate indexes on the individual columns if they are being queried individually? I read that generally having individual column indexes is faster.
    I also read that indexes on columns used in the order by clause can improve the speed.
    It's been over two decades since I did my SQL training at Oracle in Reading, so I'm probably a bit out of touch.
  5. Mouth

    Mouth Well-Known Member

    In the 3 examples given, all of your indexes used for joins or filters are single column indexes, and in all but one case they are they primary index on the table.

    When you have > 500 records being returned, then perhaps. But rarely and certainly not in your scenario of only single or double digit records.
  6. HWS

    HWS Well-Known Member

    I don't think it will make sense to add an index just because you "can", your above queries are really fast, there is nothing to complain about.

    Maybe you want to ask Tim what exactly he complains about?

Share This Page