SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    Tranceoholic lilleman's Avatar
    Join Date
    Feb 2004
    Location
    Írebro, Sweden
    Posts
    2,716
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Is my index worthless?

    Hi,

    I am working on a usersystem and came across a question which I have not been able to find an answer to. I have a table that stores the information for the authentication process. The structure can be found below.

    Code:
    CREATE TABLE `usr_authentication` (
      `user_id` mediumint(8) unsigned NOT NULL auto_increment,
      `user_nickname` varchar(25) NOT NULL default '',
      `user_password` varchar(32) NOT NULL default '',
      PRIMARY KEY  (`user_id`),
      UNIQUE KEY `user_nickname` (`user_nickname`),
      UNIQUE KEY `authentication` (`user_nickname`,`user_password`)
    ) TYPE=MyISAM;
    In my system, only users that have activated their account (I send an activation code to their email to verify that it is valid) can login. This is the structure for the table where the activation codes are stored.

    Code:
    CREATE TABLE `usr_registrations` (
      `registration_uid` mediumint(8) unsigned NOT NULL default '0',
      `registration_added` datetime NOT NULL default '0000-00-00 00:00:00',
      `registration_code` varchar(10) NOT NULL default '',
      PRIMARY KEY  (`registration_uid`),
      UNIQUE KEY `registration_code` (`registration_code`),
      UNIQUE KEY `activation` (`registration_uid`,`registration_code`)
    ) TYPE=MyISAM;
    The authentication query is found below.

    Code:
    SELECT a.user_id FROM usr_authentication AS a
      LEFT JOIN usr_registrations AS r ON r.registration_uid = a.user_id
    WHERE a.user_nickname = "foo" AND a.user_password = MD5( "bar" )
      AND r.registration_uid IS NULL
    My question is this. Is the index called authentication (located in the usr_authentication table) worthless since I have AND r.registration_uid IS NULL in my query?

    Thanks in advance!

    Yours, Erik.

  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)
    i'm pretty sure the index called authentication will not be used at all in that query
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    Tranceoholic lilleman's Avatar
    Join Date
    Feb 2004
    Location
    Írebro, Sweden
    Posts
    2,716
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    That was what I suspected. Is there any way to solve this?

    Yours, Erik.

  4. #4
    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)
    possibly

    if you would be so kind as to specify what the problem is?

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    Tranceoholic lilleman's Avatar
    Join Date
    Feb 2004
    Location
    Írebro, Sweden
    Posts
    2,716
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    I want the index to be used. That is the problem... The reason to why I want it to be used is that the authentication process would go faster then, right?

    Yours, Erik.

  6. #6
    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)
    have you done the EXPLAIN on the query yet?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    Tranceoholic lilleman's Avatar
    Join Date
    Feb 2004
    Location
    Írebro, Sweden
    Posts
    2,716
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    No, I had not thought of that... Well, this is the result I got.



    Yours, Erik.

  8. #8
    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)
    so, it looks like i was wrong, it might use the authentication index after all
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    Tranceoholic lilleman's Avatar
    Join Date
    Feb 2004
    Location
    Írebro, Sweden
    Posts
    2,716
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    Great, now I can move on.
    Thanks for your help, Rudy!

    Yours, Erik.


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
  •