SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Enthusiast
    Join Date
    Jun 2006
    Posts
    41
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Optimizing a left join

    SELECT a.x FROM a LEFT JOIN b ON a.fk = b.pk ORDER BY a.d DESC LIMIT 10;

    If I were MySQL, I would perform the above query by first ordering table a by a.d, then limiting by 10, then joining with the table b. However it seems that MySQL is doing something much much more difficult, since running this query takes 2 minutes. Here is the EXPLAIN from it, but I'm not quite sure how to read it.

    Code:
    +--------------+------+---------------+------+---------+------+------+---------------------------------+
    | table        | type | possible_keys | key  | key_len | ref  | rows | Extra                           |
    +--------------+------+---------------+------+---------+------+------+---------------------------------+
    | a            | ALL  | NULL          | NULL |    NULL | NULL | 3913 | Using temporary; Using filesort |
    | b            | ALL  | NULL          | NULL |    NULL | NULL | 9471 |                                 |
    +--------------+------+---------------+------+---------+------+------+---------------------------------+
    Can you see from this EXPLAIN whether MySQL is doing 3913*9471 operations ? I would think that sorting the 3913 record table + doing 10 seeks on the 9471 record table should be enough. I could easily force MySQL to "do the right" thing by just emulating this join with PHP code, but I wanted to know if there was some other way?

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,276
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    have you considered declaring indexes? that usually speeds things up immensely
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Enthusiast
    Join Date
    Jun 2006
    Posts
    41
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'm curious about this from a bit more generalised point of view. Even without indexes there should still be just 94710 integer comparisons and one 3913 row quicksort, if MySQL would just do the right thing.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,276
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    mysql is doing the right thing -- it is executing your query to the best of its abilities, given what it has to work with

    have you considered declaring indexes? that usually speeds things up immensely
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Enthusiast
    Join Date
    Jun 2006
    Posts
    41
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank you, I decided to add an index, even though the overall question still remains. Time to move on in development.

  6. #6
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,580
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Bemmu View Post
    Thank you, I decided to add an index, even though the overall question still remains.
    Reading every row in a table thousands of times isn't going to be fast. What's the question that still remains?


Bookmarks

Posting Permissions

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