homepage Welcome to WebmasterWorld Guest from 54.226.80.55
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Pubcon Platinum Sponsor 2014
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderators: physics

Databases Forum

    
Primary key for table with only one datafield
Table with only one datafield, what's the most efficient?
GryphonLeon




msg:3823710
 11:49 pm on Jan 10, 2009 (gmt 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!

 

LifeinAsia




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

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.

GryphonLeon




msg:3824961
 12:16 am on Jan 13, 2009 (gmt 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!

maximillianos




msg:3825238
 12:29 pm on Jan 13, 2009 (gmt 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.

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved