Welcome to WebmasterWorld Guest from 54.224.197.251

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)

New User

10+ Year Member

joined:June 5, 2005
posts: 16
votes: 0


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)

Moderator from US 

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

joined:Dec 10, 2005
posts:5702
votes: 88


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)

New User

10+ Year Member

joined:June 5, 2005
posts:16
votes: 0


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)

Senior Member

WebmasterWorld Senior Member 10+ Year Member

joined:Dec 20, 2004
posts:2377
votes: 0


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.
 

Join The Conversation

Moderators and Top Contributors

Hot Threads This Week

Featured Threads

Free SEO Tools

Hire Expert Members