I have a users table, the typical firstname, lastname, phone, email
etc..

Will have about 10,000 users.
Mostly all selects as opposed to updates, inserts

I was thinking of having the primary key as the email address field
which I have set at varchar(70) and limiting the index to the first 15
characters. Is this going to be too slow compared to an index using an
autoincremented integer?

I prefer the email address, since it has meaning and searches would be
straight forward.

Any suggestions?

My platform is : MySQL 5.0.22 /INNODB

Thanks
Roshan

roch77 (AT) gmail (DOT) com wrote:
One problem with limiting the primary key to 15 characters is you might
have different emails - but they are rejected. For instance,

membername (AT) example (DOT) org
membername (AT) example (DOT) com

Not likely, I will admit - but with 10K users it will happen. And what
happens if someone doesn't have an email address (not likely, but
possible).

Remember - the reason for a primary key is to uniquely identify a row.
That way if you do something like:

SELECT rec_id, name, email FROM member_tbl WHERE state='confusion';

Then while you're going through the records you want to change
something, you can do

UPDATE member_tbl SET state='not confused' WHERE rec_id=$rec_id;

(assuming you saved the rec_id in variable $rec_id, of course).

This would be much faster than doing the same in email.

I use the member id for the primary key in my association work. Of
course, you can still add an index to the email if you are doing
requests which match or sort on the field.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex (AT) attglobal (DOT) net
==================

Email addresses are not guaranteed unique in the first 15 characters.
There are a number of standardized ways of assigning email addresses,
often used by corporations to make name <--> email easier to do mentally,
and to avoid having a customer service supervisor with the email address
crackwhore (AT) bigcorp (DOT) com. One of these forms is:

first.last (AT) domain (DOT) com
and in my case, the first 15 characters is:
gordon.burditt@
which doesn't include ANY of the domain. Another problem is that
you'll reject more than one address that starts with:
john.smith@mail


Regardless of WHAT you use as a primary key, you're probably going
to be doing a lot of lookups by email address (or did you expect
users to memorize a member number?). You'll probably need at least
one table (the membership table) containing email address, member
number (the autoincrement key), name, etc. It will need a unique
index on the email address (remember that a primary key IS a unique
index).

If you have other tables it might be best to use the member number
as (or as part of) the primary key for those tables. If you need
lookups by email address for the other tables, do a join against
the membership table. If, for example, you have a message table
whose primary key is compound (user, message #), you may want to
avoid storing the entire email address in a record for each message,
and the "member number" is shorter.


There's not a lot of difference between a non-primary unique index
and a primary key. Question: how do you determine which child is
the primary child and which child is the twin?

Gordon L. Burditt

Ok, thanks for the replies.

I thought that by defining the primary key as the first 15 characters
of a field, the uniqueness of each row would still be the whole email
field. (not just the first 15 characters). Anyway, I see that I am
wrong and it would be better to do the integer route as the primary
key.
And I will build another index for the email addresses.

Thanks once again for clarifying.. Roshan