Table Structure

ShawnK

okay...I have searche and read and all tons of crap and I still cannot get an answer to this one. I know WHAT an primary key, index, and unique is but can someone please explain HOW an INDEX is used.

for example this is a table I have:

CREATE TABLE `users` (
`id` INT( 4 ) UNSIGNED ZEROFILL NOT NULL AUTO_INCREMENT ,
`username` VARCHAR( 20 ) NOT NULL ,
`md5pass` VARCHAR( 32 ) NOT NULL ,
`userlevel` ENUM( '0', '1', '2' ) DEFAULT '1' NOT NULL ,
`real_first` VARCHAR( 25 ) NOT NULL ,
`real_last` VARCHAR( 25 ) NOT NULL ,
`profile` LONGTEXT NOT NULL ,
PRIMARY KEY ( `id` ) 
);

I know that the PRIMARY KEY is what I can use to identify say...two people with the same first name...but I was thinking why do I need a id if I am going to have unique usernameS? I don't have that set in the table cause I have it in my code:

              $name_check = mysql_query("SELECT * FROM users WHERE username='$u_name'");
              if (mysql_num_rows($sql_username_result) > 0){
                 $loginerror = "That username has already been taken. Please pick another!";
                 header("Location: index.php?pg=create");
                 } else { //the insert crap...

So what type of key(s) would you recomend for this particular table....and where should I put'em.

Thanks a ton in advance...

TheDefender

Why use an index in a table? This is an excerpt from the MySQL manual found at MySQL.com.

Indexes are used to find rows with specific column values fast. Without an index, MySQL has to start with the first record and then read through the whole table to find the relevant rows. The bigger the table, the more this costs. If the table has an index for the columns in question, MySQL can quickly determine the position to seek to in the middle of the data file without having to look at all the data. If a table has 1,000 rows, this is at least 100 times faster than reading sequentially. Note that if you need to access almost all 1,000 rows, it is faster to read sequentially, because that minimizes disk seeks.

To quickly find the rows that match a WHERE clause.

To eliminate rows from consideration. If there is a choice between multiple indexes, MySQL normally uses the index that finds the smallest number of rows.

To retrieve rows from other tables when performing joins.

To find the MIN() or MAX() value for a specific indexed column key_col. This is optimized by a preprocessor that checks whether you are using WHERE key_part_# = constant on all key parts that occur before key_col in the index. In this case, MySQL will do a single key lookup for each MIN() or MAX() expression and replace it with a constant. If all expressions are replaced with constants, the query will return at once.

To sort or group a table if the sorting or grouping is done on a leftmost prefix of a usable key (for example, ORDER BY key_part1, key_part2). If all key parts are followed by DESC, the key is read in reverse order.

In some cases, a query can be optimized to retrieve values without consulting the data rows. If a query uses only columns from a table that are numeric and that form a leftmost prefix for some key, the selected values may be retrieved from the index tree for greater speed:

So, in your case, I would setup an INDEX on the username field, since you are planning to run queries against that field. It wouldn't hurt to make that field a unique INDEX either, even though you wrote your PHP to ensure it is unique. I like the double-failsafe personally.

In any respect, they are individually tailored per your needs, so read up, and set them up according to your own specific needs. You may find that you don't need or want INDEXES. It's up to you.