I'd suggest turning those around:
ALTER TABLE tablename ADD UNIQUE `index_name` (group, email_address)
Why? For purposes of keeping the combination of group and email unique, the order doesn't matter, but the leftmost part of the index can be used just as if it were a single column index. From what you've described, I'm expecting you'll frequently run queries like
SELECT email_address FROM yourtable WHERE group = somevalue;
An index on (group, email_address) would be used to determine which rows to select in this case, but an index on (email_address, group) would not. In fact, this particular query could be resolved solely by looking at the (group, email_address) index.
In other words, you would not need a separate index on group this way, as the unique index on (group, email_address) would both guarantee uniqueness and speed up queries based on group or group-email_address combinations.
Michael
Wesley Furgiuele wrote:
Scott:
Sorry, should have included it... http://dev.mysql.com/doc/mysql/en/CREATE_INDEX.html
The basic syntax you're looking to use is
ALTER TABLE tablename ADD UNIQUE `index_name` ( email_address, group )
Wes
On Jul 20, 2004, at 7:45 PM, Scott Haneda wrote:
on 7/20/04 4:10 PM, Wesley Furgiuele at [EMAIL PROTECTED] wrote:
Rather than make the email address column unique, since you want to allow
multiple email address instances, you can make a unique index based on
email_address+group. Wouldn't that help, or is that still too simple for your
situation?
I think you nailed it, I was not aware of this feature, any links that tell
me more?
--
-------------------------------------------------------------
Scott Haneda Tel: 415.898.2602
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
