[SOLVED] Indexes

[waynewex]

I've only began using indexes on my db. I'm able to add an index to a specific column, but I'm still not sure as to how I will be able to go about:

 

• Using them (indexes) in my queries and
  
• Updating them.
  

Could somebody give a small example. Here is one of my tables.

 

$mysqli->query("
CREATE TABLE IF NOT EXISTS users(
  user_id INT(11) NOT NULL AUTO_INCREMENT,
  username VARCHAR(22) NOT NULL UNIQUE,
  email VARCHAR(120) NOT NULL UNIQUE,
  name VARCHAR(100) NOT NULL,
  user_type TINYINT(3) NOT NULL,
  password TEXT NOT NULL,
  dob DATE NOT NULL,
  gender TINYINT(1) NOT NULL,
  location VARCHAR(100) NOT NULL,
  last_login DATETIME NOT NULL,
  signup_date DATETIME NOT NULL,
  incorrect_logins TINYINT(1) DEFAULT 3,
  INDEX email_index (email),
  INDEX username_index (username),
  INDEX user_type_index (user_type),
  INDEX name_index (name),
  INDEX location_index (location),
  INDEX signup_date_index (signup_date),
  INDEX last_login_index (last_login),
  INDEX password_index (password),
  PRIMARY KEY(user_id))") or die(mysqli_error($mysqli));

 

I've added an index to anything that I might end up searching for.

[waynewex]

Does MySQL automatically take note of the fact that you have an index on a certain column? And when updating a column; does the index update itself?

[mwasif]

Does MySQL automatically take note of the fact that you have an index on a certain column? And when updating a column; does the index update itself?

Yes.

 

Following links will give you complete understanding of indexes.

http://dev.mysql.com/doc/refman/5.0/en/mysql-indexes.html

http://www.sitepoint.com/article/optimizing-mysql-application/