Indexes

[waynew]

Say for example, I have a user table that is made like so:

 

<?php
$user = "
   CREATE TABLE IF NOT EXISTS user(
      user_id INT(11) NOT NULL AUTO_INCREMENT,
      email VARCHAR(60) NOT NULL UNIQUE,
      password VARCHAR(60) NOT NULL,
      PRIMARY KEY(user_id))";
?>

 

Would is be beneficial to add an index on both the email and the password columns (for login purposes) or would the fact that the email column has an unique index on it be enough?

 

<?php
$user = "
    CREATE TABLE IF NOT EXISTS user(
       user_id INT(11) NOT NULL AUTO_INCREMENT,
       email VARCHAR(60) NOT NULL UNIQUE,
       password VARCHAR(60) NOT NULL,
      INDEX login_index(email,password),
       PRIMARY KEY(user_id))";
?>

[Mchl]

Ask EXPLAIN

[waynew]

The one without the index is telling me that it's using filesort, whereas the one with the index is stating that it is using login_index. So I'm guessing that an index would be beneficial.

[Mchl]

You're guessing correctly.