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

Chris White 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).

When creating link tables, I just use the second one. You need a unique
index on both columns anyway; why not make it the primary key?

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

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 <chriswhite@interfuel.com> 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/mysql?unsub=drbuettner@gmail.com
>
>

Unless I completely misunderstand your question, I don't see how the "id"
field would ever be of use. You said you aren't going to be selecting on
"id", only by one or the other of association_id1 or association_id2. If you
are really worried about the importance of inserts / updates / deletes, and
not about retrievals, then don't index anything. A SELECT will have to
serially access the table, while a write will have to shuffle the indices.

Regards,

Jerry Schwartz
Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032

860.674.8796 / FAX: 860.674.8341


> -----Original Message-----
> From: Chris White [mailto:chriswhite@interfuel.com]
> Sent: Tuesday, October 03, 2006 2:20 PM
> To: mysql@lists.mysql.com
> Subject: Combined Primary Key and Auto Increment Primary Key
>
> 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/mysql?unsub=j...e-infoshop.com
>
>