Welcome to WebmasterWorld Guest from 50.16.112.199

Forum Moderators: open

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.
 

Featured Threads

My Threads

Hot Threads This Week

Hot Threads This Month