Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2012
    Posts
    4

    Unanswered: Order by rand() seems to be slow - can it made faster?

    Hi!

    This is my first post in this forum. In my sparetime I'm providing patches for the open source social network software "friendica". My goal is to make it faster. Especially concerning the SQL statements there is much to do

    I'm having speed issues with "order by rand()". The statement is this:

    Code:
    select gcontact.* from gcontact left join glink on
    glink.gcid = gcontact.id where glink.cid = 0 and glink.uid = 0 order by
    rand() limit 9;
    The execution time is mostly over 2 seconds.

    Explain tells me:
    Code:
    mysql> explain select gcontact.* from gcontact left join glink on
    glink.gcid = gcontact.id where glink.cid = 0 and glink.uid = 0 order by
    rand() limit 9;
    +----+-------------+----------+--------+---------------+---------+---------+---------------------+------+----------------------------------------------+
    | id | select_type | table    | type   | possible_keys | key     | key_len | ref                 | rows | Extra                                        |
    +----+-------------+----------+--------+---------------+---------+---------+---------------------+------+----------------------------------------------+
    |  1 | SIMPLE      | glink    | ref    | cid,uid,gcid  | uid     | 4       | const               | 2455 | Using where; Using temporary; Using filesort |
    |  1 | SIMPLE      | gcontact | eq_ref | PRIMARY       | PRIMARY | 4       | piratica.glink.gcid |    1 | Using where                                  |
    +----+-------------+----------+--------+---------------+---------+---------+---------------------+------+----------------------------------------------+
    2 rows in set (0.00 sec)
    Can I speed it up?

  2. #2
    Join Date
    Jul 2012
    Posts
    4
    Update:

    It seems that splitting up is the best idea:

    First step: Getting all IDs
    Code:
    select distinct gcid from glink where glink.cid = 0 and glink.uid = 0 order by rand() limit 9;
    Second step: Fetching the profiles:
    Code:
    select gcontact.* from gcontact where id in (338,3125,71,338,324,652,1421, ...);
    With my tests this was done in 0.02 seconds - instead of two seconds.

    Why?

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    do an EXPLAIN on the first of your two queries in post #2

    that'll show you why
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Jul 2012
    Posts
    4
    I don't see a huge difference:
    Code:
    mysql> explain select distinct gcid from glink where glink.cid = 0 and glink.uid = 0 order by rand() limit 9;
    +----+-------------+-------+------+---------------+------+---------+-------+------+----------------------------------------------+
    | id | select_type | table | type | possible_keys | key  | key_len | ref   | rows | Extra                                        |
    +----+-------------+-------+------+---------------+------+---------+-------+------+----------------------------------------------+
    |  1 | SIMPLE      | glink | ref  | cid,uid       | uid  | 4       | const | 2460 | Using where; Using temporary; Using filesort |
    +----+-------------+-------+------+---------------+------+---------+-------+------+----------------------------------------------+
    1 row in set (0.02 sec)
    Code:
    mysql> explain select gcontact.* from gcontact left join glink on
        -> glink.gcid = gcontact.id where glink.cid = 0 and glink.uid = 0 order by
        -> rand() limit 9;
    +----+-------------+----------+--------+---------------+---------+---------+---------------------+------+----------------------------------------------+
    | id | select_type | table    | type   | possible_keys | key     | key_len | ref                 | rows | Extra                                        |
    +----+-------------+----------+--------+---------------+---------+---------+---------------------+------+----------------------------------------------+
    |  1 | SIMPLE      | glink    | ref    | cid,uid,gcid  | uid     | 4       | const               | 2460 | Using where; Using temporary; Using filesort |
    |  1 | SIMPLE      | gcontact | eq_ref | PRIMARY       | PRIMARY | 4       | piratica.glink.gcid |    1 | Using where                                  |
    +----+-------------+----------+--------+---------------+---------+---------+---------------------+------+-- --------------------------------------------+
    2 rows in set (0.00 sec)

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    yeah, that actually makes sense now

    by the way, your LEFT OUTER JOINs should logically be INNER JOINs

    see if that makes a difference
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Jul 2012
    Posts
    4
    Code:
    mysql> explain select gcontact.* from gcontact inner join glink on glink.gcid = gcontact.id where glink.cid = 0 and glink.uid = 0 order by rand() limit 9;
    +----+-------------+----------+--------+---------------+---------+---------+---------------------+------+----------------------------------------------+
    | id | select_type | table    | type   | possible_keys | key     | key_len | ref                 | rows | Extra                                        |
    +----+-------------+----------+--------+---------------+---------+---------+---------------------+------+----------------------------------------------+
    |  1 | SIMPLE      | glink    | ref    | cid,uid,gcid  | uid     | 4       | const               | 2460 | Using where; Using temporary; Using filesort |
    |  1 | SIMPLE      | gcontact | eq_ref | PRIMARY       | PRIMARY | 4       | piratica.glink.gcid |    1 | Using where                                  |
    +----+-------------+----------+--------+---------------+---------+---------+---------------------+------+----------------------------------------------+
    2 rows in set (0.03 sec)
    Code:
    mysql> select gcontact.* from gcontact inner join glink on glink.gcid = gcontact.id where glink.cid = 0 and glink.uid = 0 order by rand() limit 9;
    
    ...
    
    9 rows in set (2.16 sec)
    There is no difference.

    Are there any (written) rules for performance tuning of MySQL? Like the way you should to JOINs and so on?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •