MYSQL - no primary key? use indexes?

compaqlaptop1

I have a drupal module that defines a MYSQL table with 3 columns - node id, comment id and user id - nid, cid, uid.

For this particular module there is no unique value in any of these columns. For example, the table could look like this

nid cid uid
0 1 1
0 1 5
1 3 1
2 6 1

As there is no unique value in any column I can't set a primary key on it. I read that you can set a primary key on more than one column and these columns taken together then form a unique primary key. However in my case I can only form a unique key when all three columns are taken together.

One other bit of information is my queries to this table always take the form - "SELECT uid FROM table WHERE nid = x AND cid = y"

With that in mind my questions are -
1. Is there any point to set all of these columns to be primary key? Will it improve performance over having no primary keys?

2. Would setting an index on one or more columns be a good idea here to improve performance? If so, why?

AndrewMc

compaqlaptop1 wrote: »

With that in mind my questions are -
1. Is there any point to set all of these columns to be primary key? Will it improve performance over having no primary keys?

Having all three columns as a primary key will mean that no row can occur twice. This enforces data integrity (if this is a requirement for the table), but means a small penalty for each INSERT while the index is updated and checked for uniqueness.

On the plus side, since you're only asking about numeric columns, and the columns in your WHERE clause are a leftmost prefix of the key (nid,cid is the start of the key nid,cid,uid), then the value of cid can be retrieved directly from the index. This saves MySQL from having to read anything from the table data itself.

So, based on what you've described, a primary key across all three columns should work well.

compaqlaptop1 wrote: »

2. Would setting an index on one or more columns be a good idea here to improve performance? If so, why?

Without an index, the only way to find data is to read through the whole table for every request. For any column that appears in a WHERE clause, having an index on that column improves the speed of the SELECT. (If the query is made rarely or there are other indexes in use that shrink the rows to be read sufficiently, it may not be worth the time or space to have the extra index.)

If you have two indexes, both can be used. However, when the same two columns often appear together in the WHERE clause, it's faster to have one index of the two columns, than two indexes of each column separately.

Note that there are times when MySQL won't use an index. One example is a multi-column index where the first column in the key isn't part of the WHERE clause. Another is where MySQL doesn't think that the Index will narrow down the search enough and that a straight search is more efficient.

compaqlaptop1

OK thanks for the detailed answer. You say I should have indexes on nid and cid as they appear in the WHERE clause. And that I should have one index of both columns than two indexes of each column separately...how do I do that? Can you give an example how I would specify -
1. One index of both columns
2. Two indexes of each column separately

AndrewMc

compaqlaptop1 wrote: »

OK thanks for the detailed answer. You say I should have indexes on nid and cid as they appear in the WHERE clause. And that I should have one index of both columns than two indexes of each column separately...how do I do that? Can you give an example how I would specify -
1. One index of both columns

You didn't give the name of the table, so I'm assuming it's "tablename". The index for both columns together:

CREATE INDEX myindex ON tablename (nid, cid);

compaqlaptop1 wrote: »

2. Two indexes of each column separately

CREATE INDEX myindex_nid ON tablename (nid);
CREATE INDEX myindex_cid ON tablename (cid);

Note that if you have an index on (nid, cid), then having another, separate index on nid alone is a complete waste of time. You might still need the separate index on cid if you have searches on cid while not searching on nid.