At 2:06 PM -0400 10/6/09, Dan Cech wrote:
tedd wrote:
> Dan:
That's a good point, but I still like having a "separate from data"
auto_increment index for all records. Besides adding one field to a
record, can you see any harm in that?
Cheers,
tedd
You also end up maintaining separate indexes to support the lookups you
actually want to do:
Say you want to be able to do lookups in both directions, like:
SELECT account_id FROM some_rel WHERE user_id=X
and
SELECT user_id FROM some_rel WHERE account_id=Y
The most efficient system for doing this is:
CREATE TABLE some_rel (
user_id int(11) not null,
account_id int(11) not null,
PRIMARY KEY (user_id,account_id),
KEY account_id (account_id)
);
The primary key is used for lookups by user_id, and the second key for
lookups via account_id.
If you add an auto_increment column, you then have to do:
CREATE TABLE some_rel (
some_rel_id int(11) not null auto_increment,
user_id int(11) not null,
account_id int(11) not null,
PRIMARY KEY (some_rel_id),
UNIQUE KEY user_id (user_id,account_id),
KEY account_id (account_id)
);
You need a unique index on user_id,account_id to enforce the
relationship rule.
The account_id lookup cannot leverage the primary key, so you either
have to add the user_id field to that index or accept that it's going to
have to read the primary key and look up the user_ids from the table itself.
Dan
Dan:
Interesting consideration.
As I said, I typically have a "separate from data" id that pertains
to the individual record. A many:many record may not be unique, but
may include a date field or other such distinguishing character. As
such, I usually like getting/setting such records by their ids rather
than using multiple fields. Of course, you get the id by using
multiple fields, but then dealing with the record itself, you have a
unique number and I find that simpler.
Now does that led to a faster database lookup? Probably not, but that
may be the price I pay for keeping things simple for me.
Do you see any way for me to be happy and my db queries fast?
Cheers,
tedd
--
-------
http://sperling.com http://ancientstones.com http://earthstones.com
_______________________________________________
New York PHP Users Group Community Talk Mailing List
http://lists.nyphp.org/mailman/listinfo/talk
http://www.nyphp.org/Show-Participation
