NYCPHP Meetup

[tedd]

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