Welcome to WebmasterWorld Guest from 54.161.187.250

Forum Moderators: open

Message Too Old, No Replies

Primary key for table with only one datafield

Table with only one datafield, what's the most efficient?

   
11:49 pm on Jan 10, 2009 (gmt 0)

5+ Year Member



Hi everyone,

A simple question probably..

I want to create a mysql table to store email addresses of people who don't want to receive emails.

The table should only have to store the email addresses, no other information such as a member id is needed.

I will have to do a SELECT query on the table quite often to see if an email address exists in the table.

For the past few years, I have always used this:

CREATE TABLE `em_opt_out` (
`em_id` mediumint(8) unsigned NOT NULL auto_increment,
`email` varchar(70) NOT NULL default '',
PRIMARY KEY (`em_id`)
) ENGINE=MyISAM;

and then the mysql select query in PHP:
$total = mysql_result(mysql_query("SELECT COUNT(em_id) FROM em_opt_out WHERE email='".quote_smart($email)."'"),0);

Although this works perfectly fine, I have the feeling it is not the most efficient, since I've defined the em_id auto-increment, but never use it.

What would you guys suggest? Is it better to do this for example?

CREATE TABLE `em_opt_out` (
`email` varchar(70) NOT NULL default '',
PRIMARY KEY (`email`)
) ENGINE=MyISAM;

and then the mysql select query in PHP:
$total = mysql_result(mysql_query("SELECT COUNT(email) FROM em_opt_out WHERE email='".quote_smart($email)."'"),0);

Thanks in advance for your help!

4:36 pm on Jan 12, 2009 (gmt 0)

WebmasterWorld Administrator lifeinasia is a WebmasterWorld Top Contributor of All Time 5+ Year Member Top Contributors Of The Month



If you make email a primary key, you will never be able to add the same e-mail address twice. That's not necessarily a bad thing, but it will require some extra error checking code in case you (or a user) tries to add an e-mail that's already in the table.

There's certainly nothing wrong with the original design. At this point, I'm not sure it's worth the added work to change things.

12:16 am on Jan 13, 2009 (gmt 0)

5+ Year Member



Thanks LifeinAsia, I'm glad to hear there's nothing wrong with the original design. I guess I'll stick to that then!

The extra error checking for inserting a new record in the second example wouldn't have been a problem though, an insert query would be very rare, whereas the select would be very frequent.

Thanks again!

12:29 pm on Jan 13, 2009 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member



I would leave it as-is but create an index on the table using the email field. This will allow for much faster search and retrieval.