Re: indexing unique fields
I think that KEY is the same as INDEX, but why UNIQUE? - Original Message - From: TJ Hunter [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, February 12, 2001 3:49 AM Subject: indexing unique fields I'm using the following table to store information about a merchant user. He uses his email address as his username to login. Since I'm going to be doing WHERE clauses on that field, I'd like it to be indexed for speed. If I have a UNIQUE property on the email field when I create the table, do I need the "INDEX (email)" line in there or since the email field is UNIQUE, it's automatically indexed? CREATE TABLE merchants ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, nameCHAR(64) NOT NULL, contact CHAR(64), address CHAR(64), cityCHAR(64), stateId INT UNSIGNED, zipcode CHAR(11), phone CHAR(15), fax CHAR(15), email CHAR(64) NOT NULL UNIQUE, passwordCHAR(64) NOT NULL, PRIMARY KEY (id), INDEX (email) ); This question came up when I did a dump on the table and got the output below. There is a 'KEY email_2 (email)' line that is throwing me off. CREATE TABLE merchants ( id int(10) unsigned NOT NULL auto_increment, name char(64) NOT NULL, contact char(64), address char(64), city char(64), stateId int(10) unsigned, zipcode char(11), phone char(15), fax char(15), email char(64) NOT NULL, password char(64) NOT NULL, PRIMARY KEY (id), UNIQUE email (email), KEY email_2 (email) ); -TJ Hunter - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: indexing unique fields
You don't need the INDEX (email) at the end of this statement, it's creating a second index on the same data. UNIQUE specifies that an index should be created. TJ Hunter [EMAIL PROTECTED] wrote: I'm using the following table to store information about a merchant user. He uses his email address as his username to login. Since I'm going to be doing WHERE clauses on that field, I'd like it to be indexed for speed. If I have a UNIQUE property on the email field when I create the table, do I need the "INDEX (email)" line in there or since the email field is UNIQUE, it's automatically indexed? CREATE TABLE merchants ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, nameCHAR(64) NOT NULL, contact CHAR(64), address CHAR(64), cityCHAR(64), stateId INT UNSIGNED, zipcode CHAR(11), phone CHAR(15), fax CHAR(15), email CHAR(64) NOT NULL UNIQUE, passwordCHAR(64) NOT NULL, PRIMARY KEY (id), INDEX (email) ); This question came up when I did a dump on the table and got the output below. There is a 'KEY email_2 (email)' line that is throwing me off. CREATE TABLE merchants ( id int(10) unsigned NOT NULL auto_increment, name char(64) NOT NULL, contact char(64), address char(64), city char(64), stateId int(10) unsigned, zipcode char(11), phone char(15), fax char(15), email char(64) NOT NULL, password char(64) NOT NULL, PRIMARY KEY (id), UNIQUE email (email), KEY email_2 (email) ); -TJ Hunter - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
