...

View Full Version : PK over three columns, or new column for PK?



shaunthomson
03-30-2013, 02:38 PM
Gidday

I have a table of 5 columns. The first three are together unique, so they could be the primary key. Or, I could create an auto incrementing id column as the PK.

My question is - would it be faster to make a query on the three column PK, or on the single ID column PK? And how would that 3 column query look - like this...?


SELECT * FROM mytable WHERE col1 = 'blah' AND col2 = 'blahblah' AND col3 = 'blahblahblah'

I ask, as I see a lot of posts around saying 'why'd you make a new id column when you could use the unique columns as the PK?'

The other thing is, I'll be referencing rows in this table from other tables, so I'm thinking it's better so have an id column that I can refer to in the other tables.

Thanks guys.

Old Pedant
03-31-2013, 12:17 AM
As a general rule, you should not use an autoincrement column for a PK if a natural PK exists. There can be exceptions. For example, if the table in question will then be referenced via foreign key by some other table, it might make sense to have the autonumber column for compactness and efficiency of the foreign key.

Even if that is true, though, there's no reason you couldn't create a UNIQUE index on the 3 columns, and there are many reasons to do so. With MySQL, there's not too much difference between a PRIMARY key and a UNIQUE index (not true in other DBs; in SQL Serve, the PRIMARY key actually influences strongly how the table is stored on disk).

But caution: With MySQL, composite keys have some nasty limitations.

An example:


CREATE TABLE foo (
fieldA varchar(20),
fieldB datetime,
fieldC int,
PRIMARY KEY (fieldA, fieldB, fieldC)
);

If you were to then do the following queries, MySQL *would*use the primary key:


SELECT ... WHERE fieldA='x' AND fieldB = '2013-1-1' AND fieldC=7
SELECT ... WHERE fieldA='x' AND fieldB = '2013-1-1'
SELECT ... WHERE fieldA='x'

But if you did any of the following, MySQL *CAN NOT* use the primary key to speed queries:


SELECT ... WHERE fieldB = '2013-1-1' AND fieldC=7
SELECT ... WHERE fieldC=7

And if you did this query, MySQL would only use the PK for fieldA, *not* for fieldC:


SELECT ... WHERE fieldA='x' AND fieldC=7

All this is because MySQL can *ONLY* use the fields in the composite key in left-to-right order, without skiipping any.

felgall
03-31-2013, 12:24 AM
All this is because MySQL can *ONLY* use the fields in the composite key in left-to-right order, without skiipping any.

But there's nothing to stop you making an additional key on those fields if you are going to need to access by fieldA/C as well as by fieldA/B

Old Pedant
03-31-2013, 01:16 AM
But there's nothing to stop you making an additional key on those fields if you are going to need to access by fieldA/C as well as by fieldA/B

Absolutely!

But then the question arises as to whether you are better off with a single compound index or three separate indexes or the compound key plus one index or...

And with MySQL, there's no good answer to that except to try variations and benchmark them in your actual situation. And then you also have the fun of learning that sometimes MySQL will purposely choose not to use an available index because it thinks a full table scan will be more efficient. And sometimes MySQL is right and sometimes it is not. Luckily, there are ways to gently guide MySQL to the best answer, but it takes experimentation to know just what is the best answer.

shaunthomson
03-31-2013, 04:44 AM
Thanks guys.

I'm referencing that table in a tagmap table, plus a couple other tables, so I'll go with the AI column, but also have a unique index over those other three columns, and be mindful my queries on those columns go left to right without skipping any columns (and add separate indexes for queries that do need to start elsewhere than the leftmost composite key column).

Old Pedant
03-31-2013, 11:56 PM
Sound like you have it fully covered, than. If you ever have any doubt, though, just use MySQL EXPLAIN command.

shaunthomson
04-04-2013, 06:24 PM
Cheers Old Pedant

I'd never looked at EXPLAIN before.

Found a good starter here:

http://phpmaster.com/using-explain-to-write-better-mysql-queries/



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum