Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 4 of 4
  1. #1
    Regular Coder
    Join Date
    Aug 2002
    Posts
    151
    Thanks
    0
    Thanked 0 Times in 0 Posts

    How to improve this query

    I have a simple query that is showing up in a log as sometimes taking a long time. The query is below (with explain in front of it). 'code' table has 40 thousand rows. The 'user' table has about 50 thousand rows.

    What should I do either by altering the query or adding indexes to speed things up?


    Code:
    explain SELECT * FROM `user`,code WHERE `user`.user_id = code.user_id
    AND code.code = '50816ef9621b4142760090e928d22658e79e0415d1cad824bdb43';
    Output is
    Code:
    >> +----+-------------+-------+--------+---------------+---------+---------+----------------------+-------+-------------+
    >> | id | select_type | table | type   | possible_keys | key     | key_len | ref                 | rows  | Extra       |
    >> +----+-------------+-------+--------+---------------+---------+---------+----------------------+-------+-------------+
    >> |  1 | SIMPLE      | code | ALL    | user_id       | NULL    | NULL    | NULL                | 35696 | Using where | 
    >> |  1 | SIMPLE      | user  | eq_ref | PRIMARY       | PRIMARY | 4       | mydb.code.user_id |     1 |             | 
    >> +----+-------------+-------+--------+---------------+---------+---------+----------------------+-------+-------------+
    >> 2 rows in set (10.11 sec)

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,650
    Thanks
    80
    Thanked 4,638 Times in 4,600 Posts
    As you can see, it says that it uses *NO* key when accessing the CODE table.

    It is scanning *ALL* rows of the table.

    Presumably, MySQL has decided that using the user_id key would not help performance. *PROBABLY* MySQL is wrong in this case, and you probably could use STRAIGHT_JOIN to force it to use the index:
    Code:
    SELECT * FROM `user` STRAIGHT_JOIN code 
    ON `user`.user_id = code.user_id
    WHERE code.code = '50816ef9621b4142760090e928d22658e79e0415d1cad824bdb43';
    (Though shame on you for using SELECT * instead of selecting only the needed fields.)

    In the long run, though, you will surely get the best performance by adding an index on the CODE.CODE column.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #3
    Regular Coder
    Join Date
    Aug 2002
    Posts
    151
    Thanks
    0
    Thanked 0 Times in 0 Posts
    In the long run, though, you will surely get the best performance by adding an index on the CODE.CODE column.
    Thanks. So it should be a 'unique' index correct?

    I also find that the code table often needs to be 'optimized' because it has a little red in the overhead column under phpmyadmin but is this just to be expected with a table that's being written to and rows removed frequently?

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,650
    Thanks
    80
    Thanked 4,638 Times in 4,600 Posts
    Quote Originally Posted by quadrant6 View Post
    Thanks. So it should be a 'unique' index correct?
    How can I know? Yes, if the code is supposed to be unique IN THE ENTIRE TABLE. No, if duplicates are allowed. That's up to you.

    I also find that the code table often needs to be 'optimized' because it has a little red in the overhead column under phpmyadmin but is this just to be expected with a table that's being written to and rows removed frequently?
    If you want to believe phpmyadmin, that's up to you.

    Personally, I compact my tables about once a year or so, whether they need it or not. (And they usually don't.)

    It's nearly impossible for a table that is being updated frequently to be "optimized" at all times. Most of the time, you should just let MySQL do its thing. It does a pretty good job at reusing space.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.


  •  

    Posting Permissions

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