SitePoint Sponsor

User Tag List

Results 1 to 2 of 2

Thread: Indexes??

  1. #1
    SitePoint Enthusiast
    Join Date
    Mar 2005
    Posts
    78
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Indexes??

    I added two indexes to a table:

    index userID_active (on columns userID and active)
    index userID (on column userID)

    When I didn't have any indexes this is what explain told me:

    mysql> explain select * from welcome where userID = 1 and active = 1;
    +----+-------------+---------+------+---------------+------+---------+------+------+-------------+
    | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
    +----+-------------+---------+------+---------------+------+---------+------+------+-------------+
    | 1 | SIMPLE | welcome | ALL | NULL | NULL | NULL | NULL | 9 | Using where |
    +----+-------------+---------+------+---------------+------+---------+------+------+-------------+
    1 row in set (0.00 sec)

    When I had userID_active:

    mysql> explain select * from welcome where userID = 1 and active = 1;
    +----+-------------+---------+------+----------------------+---------------+---------+-------------+------+-------------+
    | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
    +----+-------------+---------+------+----------------------+---------------+---------+-------------+------+-------------+
    | 1 | SIMPLE | welcome | ref | userID_active,userID | userID_active | 4 | const,const | 1 | Using where |
    +----+-------------+---------+------+----------------------+---------------+---------+-------------+------+-------------+
    1 row in set (0.01 sec)


    But the problem now is, when I do the following:
    mysql> explain select * from welcome where userID = 1;
    +----+-------------+---------+------+----------------------+---------------+---------+-------+------+-------------+
    | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
    +----+-------------+---------+------+----------------------+---------------+---------+-------+------+-------------+
    | 1 | SIMPLE | welcome | ref | userID_active,userID | userID_active | 3 | const | 4 | Using where |
    +----+-------------+---------+------+----------------------+---------------+---------+-------+------+-------------+
    1 row in set (0.01 sec)

    i.e. I only use the column userID in the where part of the query, yet it still selects the index userID_active.

    Why doesn't it use index userID?

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    maybe because when the optimizer searches for the existence of indexes with userID as the leftmost column, userID_active is the first one it runs across, and any index will do

    maybe because userID_active reveals how many rows will be returned, whereas the index on userID only does not show this (it shows merely one pointer into leaf nodes or something)

    as you can tell, i'm guessing

    if you do a lot of inserts and deletes, you probably don't want two indexes if one of them is not necessary

    perhaps you could run some timings for when the table has only one index of the two, and see which one's faster
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •