MySQL Primary Keys

[Cybane]

I am having trouble with a database I am creating. My only problem is that I have 3 parts of a table that I want them to all be the primary key. Is there a way to lump them together so that it is one primary key? Thanks

Cybane

[grahamstewart]

You have three entries in each row and each of the entries can uniquely identify the row?

A bit more information about the table might help, but I would say choose one entry as the primary key and just place indexes on the other two.

Or do you mean that the row can only be uniquely identified by looking at the combination of all three keys?

[Netizen]

Something like

CREATE TABLE mytable ( 
 field1 CHAR(10) NOT NULL, 
 field2 CHAR(20) NOT NULL, 
 field3 CHAR(20) NOT NULL, 
 field4 INT UNSIGNED, 
 PRIMARY KEY (field1,field2,field3) 
); 

should do it

[webadept]

Netizen's answer is probably okay, just know that if you are using a table set up like that to add rows to, it is going to be very slow. Your SELECTS will blaze, but adding, deleteing or updateing is going to drag.

Typically you should try to keep your indexing as little as possible on high write tables. It's a balance thing, and takes some work throughs.

Here are two sites which have tutorials on this.

[databasejournal.com...]

and
[devshed.com...]

[Netizen]

Well, it's only one index, not three....(just a bigger index). The main reason to use PRIMARY KEY as opposed to UNIQUE, say, would be if you want to REPLACE into the table using the primary key constraints.

webadept is correct in that having a large number of indexes on a "write" table will slow inserts down.

[phaze]

Primary keys are unique. And replace also allows you to choose which unique index you want to use to make the replace decision.

Don't worry about insert speed for now. I've found I can get decent speed inserting over 2000 records on a fulltext index with over 100,000 entries with a little db tuning voodoo.

In mysql fixed tables (tables that don't have varchar, text or blob types) are MUCH faster than dynamic tables, so where possible, try to use char(20) rather than varchar(20). The performance is faster but the cost is a little bit more disk space.

It really depends what you want to do. If you want to be able to query your table on any one of the keys then PRIMARY KEY (field1,field2,field3) is not the answer. The above will give you performance increases on searches that specify (field1) or (field1, field2) or (field1, field2, field3) but not if you're only searching on field2 or field3 for example. See here for details:

[mysql.com...]

If you want to search on all three columns individually, create indexes on all three columns and make them unique.

Mark.