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

Slow Query

Discussion in 'Troubleshooting and Problems' started by FabioCesar, Mar 1, 2012.

  1. FabioCesar

    FabioCesar Member

    # Time: 120301 9:58:30
    # User@Host: xfuser[xfuser] @ localhost []
    # Thread_id: 477815 Schema: plusgsm_xf QC_hit: No
    # Query_time: 16.573269 Lock_time: 0.000020 Rows_sent: 20 Rows_examined: 2371700
    SET timestamp=1330606710;
    SELECT user.*
    ,
    user_profile.*,
    user_option.*,
    user_privacy.*
    FROM xf_user AS user

    INNER JOIN xf_user_profile AS user_profile ON
    (user_profile.user_id = user.user_id)
    INNER JOIN xf_user_option AS user_option ON
    (user_option.user_id = user.user_id)
    INNER JOIN xf_user_privacy AS user_privacy ON
    (user_privacy.user_id = user.user_id)
    WHERE (user.user_state = 'valid') AND (user.is_banned = 0)
    ORDER BY user.username
    LIMIT 20 OFFSET 474120;
     
  2. Ghan_04

    Ghan_04 Active Member

    You're joining four tables together and running some conditions on the result as well as a sort. I would expect that to be relatively slow in any case and really slow if data is not cached. Try running an EXPLAIN SELECT against that query and see what MySQL tells you.
     
  3. EasyTarget

    EasyTarget Well-Known Member

    I am not sure why you would ever want to pull back all of the fields from the four tables, but Ok, whatever.
    If you have a large user table you need to create a temp table before joining it.

    Code:
    SELECT    user.*,
        xf_user_profile.*,
        xf_user_option.*,
        xf_user_privacy.*
    FROM    (
        select  xf_user.*
        FROM    xf_user
        WHERE    xf_user.is_banned = 0
            AND xf_user.user_state = 'valid'
        LIMIT    20 OFFSET 4710
        ) AS user
    LEFT JOIN    xf_user_profile
            ON user.user_id = xf_user_profile.user_id
    LEFT JOIN    xf_user_option
            ON user.user_id = xf_user_option.user_id
    LEFT JOIN    xf_user_privacy
            ON user.user_id = xf_user_privacy.user_id;
    The way you wrote your query, it was taking about 12 seconds to process, by using the temp table, it should take less than 1/10 of a second.

    Best of luck to you.
     
  4. FabioCesar

    FabioCesar Member

    Actually I did not write this query, it came in xenforo .. As I leave enabled the slow-log mysql did this result ... My big problem now is to locate where it is.

    My user table has more than 470,000 records with seven custom fields
     
  5. Luke F

    Luke F Well-Known Member

    Your mysql is probably misconfigured (likely not using anywhere near as much RAM as it should)

    That kind of query should be nearly instant even on a table that size. The query itself seems perfectly optimised
     
    Digital Doctor likes this.
  6. FabioCesar

    FabioCesar Member

    first query 9.52 seconds
    second query 0.02 seconds
    xf_user - 475,275 rows - 77mb
    xf_user_filed - 2,850,249 rows - 108mb
    xf_user_option - 475,275 rows - 13,7mb
    xf_user_privacy - 475,275 rows - 9,2mb

    is the server configuration or query that is poorly designed for a larger volume?
     
  7. Luke F

    Luke F Well-Known Member

    That's likely the query cache kicking in

    Can you paste your my.cnf?
     
  8. FabioCesar

    FabioCesar Member

    others query from : http://xenforo.com/community/members/
    is it really necessary to select one of all the fields in the users table to only display the number of posts the user, or the registration date?

    anyone who understands the basics of programming that makes querying know just what you want to use, generating queries faster even with a greater volume of data
     
  9. FabioCesar

    FabioCesar Member

    my.cnf
     
  10. Luke F

    Luke F Well-Known Member

    Well nothing majorly wrong there that I can see apart from the lack of innodb, which doesn't appear to the be cause of this issue (going by lock_time)

    It's a nasty filesort from the order by user.username, perhaps try increasing key_buffer_size if it's not big enough (with 2 million users it's quite likely)
     
  11. EasyTarget

    EasyTarget Well-Known Member

    The advantage of creating the temp table is that since you only need twenty records, you aren't creating massive hash table lookups, matching all of the user id's. It is a badly written query for large user tables.

    No. The query I wrote limits things to 20 records.

    it comes from the /library/XenForo/Model/User.php
    Line #413
    public function prepareUserFetchOptions(array $fetchOptions)
    They wrote a factory function to join the tables, depending on fetchOptions.
    Which will cause some queries to generate ugly, ugly joins.

    However on line #429 the comment states:
    // TODO: optimise the join on user_option with serialization to user or user_profile

    So Kier and Mike are aware of the issue they just haven't addressed it yet.
     
  12. Luke F

    Luke F Well-Known Member

    You can't limit things to 20 records and have the order by username any more efficiently than is already there

    Unless mysql does something silly like performing the joins and then doing the order by, but I would expect it to optimise that out. If that's not the case then fair enough with regards to your query
     
  13. Mike

    Mike XenForo Developer Staff Member

    The joins are fine - they're pkey based. It's not directly the joins that's slow - it's a full table scan query. Unfortunately, MySQL (pre-5.6) is rather stupid when it comes to how it executes this query, by only applying the limit at the end. 5.6 has optimizations for this AFAIK, though that will just limit the data.

    So while retrieving the data does slow it down, ultimately it still has to go through hundreds of thousands of rows. Early pages will be fast because of the index on username, so it can just stop when it finds what it needs.

    But really, this is just a great example of why the member list is junk. Aside from being completely useless with that many users, it becomes rather easy to end up with queries like this.
     
    Xon, digitalpoint and Luke F like this.
  14. xmlxp

    xmlxp Active Member

    Are you saying a large size community should disable/remove member list?
     
  15. Luke F

    Luke F Well-Known Member

    Might be an idea to have an option to control what groups appear on it if there's not already, would make it actually useful on large sites and would allow for a much more efficient query
     
  16. Mike

    Mike XenForo Developer Staff Member

    Probably, as it's not useful anyway. :)

    That will actually make it worse, as right now MySQL can use the username index to help it (in the earlier pages). But that version would have to query the group relation table to determine if a user is to be shown... though I suppose if MySQL started with that table and the result was suitably small, it could be faster.
     
  17. EasyTarget

    EasyTarget Well-Known Member

    Considering most people are stuck on MySQL 5.1; we need to help those people out.

    Originally, I didn't notice we were sorting on xf_user.username. Sorting on the username is going to cause a filesort, it is, what it is. And because of the way it must be done, MySQL is going to scan the hash twice; during the where clause and the value pairs.
    Use show session status like "Handler_read%"; to confirm the # of reads.

    However that isn't the real issue, the performance hit can really come if there isn't enough memory (sort_buffer_size) and MySQL needs to write to disk during the scan.

    SO.........
    Change the structure of the database to have
    Key(user_id, username_id)
    OR
    Write a better query; without a need to change the structure of the table.

    Code:
    SELECT    squser.*,
        xf_user.*,
        xf_user_profile.*,
        xf_user_option.*,
        xf_user_privacy.*
    FROM    (
        select  xf_user.user_id
        FROM    xf_user
        WHERE    xf_user.is_banned = 0
            AND xf_user.user_state = 'valid'
        ORDER BY xf_user.username
        LIMIT    20 OFFSET 470000
        ) AS squser
    LEFT JOIN    xf_user
            ON squser.user_id = xf_user.user_id
    LEFT JOIN    xf_user_profile
            ON squser.user_id = xf_user_profile.user_id
    LEFT JOIN    xf_user_option
            ON squser.user_id = xf_user_option.user_id
    LEFT JOIN    xf_user_privacy
            ON squser.user_id = xf_user_privacy.user_id;
    Don't take my word for it. Test it. It works. The small penalty for using the sub-query is more than made up for it when doing deep page offsets.

    For reference see:
    http://dev.mysql.com/doc/refman/5.0/en/order-by-optimization.html : Description of how file sort works
    http://www.codinghorror.com/blog/2009/06/all-abstractions-are-failed-abstractions.html
    http://www.facebook.com/note.php?note_id=206034210932
    http://www.xarg.org/2011/10/optimized-pagination-using-mysql/
    They can explain things better than I can.
     
  18. digitalpoint

    digitalpoint Well-Known Member

    Imagine if Facebook had some master memberlist you could page through. Sorting ~1,000,000,000,000 records in order to output a page of 20 users. :)

    The memberlist is more or less useless for all but the smallest sites. When was the last time you used it for something productive like finding a member that you couldn't remember their username? The #1 use for it on my site was spam bots using it to harvest all our usernames so it could later go and try to send mass PM spam.
     
    Russ likes this.
  19. xmlxp

    xmlxp Active Member

    A good tip taken from this post, member list is removed now from my forum to keep server load down
     
  20. Luke F

    Luke F Well-Known Member

    Also something I've always wondered, it would almost be preferable to display the members page without any ordering (just in order of id) - that way there wouldn't be a page that consisted of nothing but members whose names start with obscure symbols which looks terrible to the eye.

    And the user group filter would be great too, maybe even make it a frontend thing defaulting to (configurable) admins/moderators etc, because unless I'm mistaken there's no easy way of finding a list of staff besides staff online now.
     

Share This Page