Chris, I'd opt for the first, but with an index on each of
association_id1 and association_id2.
I like always having an identity column to be able to remove or update
an individual entry easily. But for speed, you'll want indexes on the
other columns.
I would either do no multi-column indexes, or do two - one each way -
since you say you'll be selecting on one or the other. If you do one
multi-column, but then select only on the 2nd column in the index, the
index will do you no good.
In most cases the overhead of additional indexes isn't too bad. Only
in extreme cases does it end up being a major consideration, IMHO.
HTH,
Dan
On 10/3/06, Chris White <[EMAIL PROTECTED]> wrote:
Hi all,
I had somewhat of a performance question. I have an association table with 2
unique values which will always be selected by one of the values (never by
id). That said, I'm wondering which would be a better gain, having this:
CREATE TABLE association_sample (
`id` INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
association_id1 INTEGER NOT NULL,
association_id2 INTEGER NOT NULL
)ENGINE=InnoDb;
or
CREATE TABLE association_sample (
association_id1 INTEGER NOT NULL,
association_id2 INTEGER NOT NULL,
PRIMARY KEY(association_id1,association_id2)
)ENGINE=InnoDb;
note that this table will mostly consist of table writes (updates, inserts,
deletes).
--
Chris White
PHP Programmer
Interfuel
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
