Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am build a web application (project management system) and I have been wondering about this when it come to performance.

I have an Issues table an inside it there are 12 foreign keys linking to various other tables. of those, 8 of them I would need to join to get the title field from the other tables in order for the record to make any sense in a web application but then means doing 8 joins which seems really excessive especially since I am only pulling in 1 field for each of those joins.

Now I have also been told to use a auto incrementing primary key (unless sharding is a concerns in which case I should use a GUID) for permanence reasons but how bad is it to use a varchar (max length 32) performance wise? I mean most of these table are probably not going to have at many records (most of them should be under 20). Also if I use the title as the primary key, I won't have to do joins 95% of the time so for 95% of the sql, I would even occur any performance hit (I think). The only downside I can think of is that I have is I will have higher disk space usage (but down a day is that really a big deal).

The reason I am use lookup tables for a lot of this stuff instead of enums is because I need all of these values to be configurable by the end user through the application itself.

What are the downsides of using a varchar as the primary key for a table not excepted to have many records?

UPDATE - Some Tests

So I decided to do some basic tests on this stuff. I have 100000 records and these are the base queries:

Base VARCHAR FK Query

SELECT i.id, i.key, i.title, i.reporterUserUsername, i.assignedUserUsername, i.projectTitle, 
i.ProjectComponentTitle, i.affectedProjectVersionTitle, i.originalFixedProjectVersionTitle, 
i.fixedProjectVersionTitle, i.durationEstimate, i.storyPoints, i.dueDate, 
i.issueSecurityLevelId, i.creatorUserUsername, i.createdTimestamp, 
i.updatedTimestamp, i.issueTypeId, i.issueStatusId
FROM ProjectManagement.Issues i

Base INT FK Query

SELECT i.id, i.key, i.title, ru.username as reporterUserUsername, 
au.username as assignedUserUsername, p.title as projectTitle, 
pc.title as ProjectComponentTitle, pva.title as affectedProjectVersionTitle, 
pvo.title as originalFixedProjectVersionTitle, pvf.title as fixedProjectVersionTitle, 
i.durationEstimate, i.storyPoints, i.dueDate, isl.title as issueSecurityLevelId, 
cu.username as creatorUserUsername, i.createdTimestamp, i.updatedTimestamp, 
it.title as issueTypeId, is.title as issueStatusId
FROM ProjectManagement2.Issues i
INNER JOIN ProjectManagement2.IssueTypes `it` ON it.id = i.issueTypeId
INNER JOIN ProjectManagement2.IssueStatuses `is` ON is.id = i.issueStatusId
INNER JOIN ProjectManagement2.Users `ru` ON ru.id = i.reporterUserId
INNER JOIN ProjectManagement2.Users `au` ON au.id = i.assignedUserId
INNER JOIN ProjectManagement2.Users `cu` ON cu.id = i.creatorUserId
INNER JOIN ProjectManagement2.Projects `p` ON p.id = i.projectId
INNER JOIN ProjectManagement2.`ProjectComponents` `pc` ON pc.id = i.projectComponentId
INNER JOIN ProjectManagement2.ProjectVersions `pva` ON pva.id = i.affectedProjectVersionId
INNER JOIN ProjectManagement2.ProjectVersions `pvo` ON pvo.id = i.originalFixedProjectVersionId
INNER JOIN ProjectManagement2.ProjectVersions `pvf` ON pvf.id = i.fixedProjectVersionId
INNER JOIN ProjectManagement2.IssueSecurityLevels isl ON isl.id = i.issueSecurityLevelId

I also ran these query with the following additions:

  • Select specific item (where i.key = 43298)
  • Group by i.id
  • Order by (it.title for int FK, i.issueTypeId for varchar FK)
  • Limit (50000, 100)
  • Group and limit together
  • Group, order, and limit together

The results for these where:

QUERY TYPE : VARCHAR FK TIME / INT FK TIME


Base query: ~4ms / ~52ms

Select specific item : ~140ms / ~250ms

Group by i.id : ~4ms / ~2.8sec

Order by : ~231ms / ~2sec

Limit : ~67ms / ~343ms

Group and limit together : ~504ms / ~2sec

Group, order, and limit together : ~504ms /~2.3sec

Now I don't know what configuration I could make to make one or the other (or both) faster but it seems like the VARCHAR FK see faster in queries for data (sometimes a lot faster).

I guess I have to choice whether that speed improvement is worth the extra data/index size.

share|improve this question
    
Your testing indicates something. I would also test with various InnoDB settings (buffer pools, etc.) because default MySQL settings are not really optimised for InnoDB. –  ypercube Apr 1 '12 at 7:24
    
You should also test Insert/Update/Delete performance as this can be affected too by the index size. The one clustered key of every InnoDB table is usually the PK and this (PK) column is also included in every other index. This is probably one big downside of big PKs in InnoDB and many indexes on the table (but 32 bytes is rather medium, not big, so it may not be a problem). –  ypercube Apr 1 '12 at 7:27
    
You should also test with bigger tables (in the range of say 10-100M rows, or bigger), if you expect your tables may grow higher than 100K (which is not really big). –  ypercube Apr 1 '12 at 7:31
    
@ypercube So I increase the data to 2 million and the select statement for the int FK get slower exponentially where the varchar foreign key remain pretty steady. A think that the varchar is worth the price in disk/memory requirements for the gain in select queries (which is going to be critical on this particular table and a few others). –  ryanzec Apr 1 '12 at 10:03
    
Just check your db (and particularly InnoDB) settings, too, before coming into conclusions. With small reference tables, I wouldn't expect exponential increase –  ypercube Apr 1 '12 at 10:35

4 Answers 4

In addition to @atxdba answer - which explained you why using numeric would be better for disk space I wanted to add two point :

  1. If your Issues table is VARCHAR FK based, and let's say you have 20 small VARCHAR(32) FK, your record can get to 20x32bytes length, while as your mentionned the other tables are lookup tables, so INT FK could be TINYINT FK which make for 20 fields a 20 bytes records. I know for several hundreds records it won't change much but when you'll get to several million I guess you'll appreciate to space saving

  2. For the speed issue I would consider using covering indexes, as it seems for this query you are not retrieving that much amount of data from lookup tables I would go for covering index and do once again the test your provided with VARCHAR FK / W/ COVERING INDEX AND regular INT FK.

Hope it could help,

share|improve this answer

In your tests you are not comparing varchar vs int keys performance difference but rather cost of multiple joins. It's not surprising that querying 1 table is faster than joining many tables.
One downside of varchar primary key is increasing index size as atxdba pointed out. Even if your lookup table doesn't have any other indexes except PK(which is quite unlikely,but possible), each table that references lookup will have an index on this column.
Another bad thing about natural primary keys, is that their value can change that causes lots of cascading updates. Not all RDMS, for instance Oracle, even let you have on update cascade. In general, changing primary key value considering as a very bad practice. I don't want to say that natural primary keys are always evil; if lookup values are small and never change I think the may be acceptable.

One option you may want to consider is to implement materialized view . Mysql doesn't support it directly, but you can achieve desired functionality with triggers on underlying tables. So you will have one table that has everything you need to display. Also, if performance is acceptable, don't struggle with the problem that doesn't exist at the moment.

share|improve this answer

I follow the following rules for primary keys:

a) Should not have any business meaning - they should be totally independent of the application you are developing, therefore I go for numeric auto generated integers. However if you need additional columns to be unique then create unique indexes to support that

b) Should perform in joins - joining to varchars vs integers is about 2x to 3x slower as the length of the primary key grows, so you want to have your keys as integers. Since all computer systems are binary, I suspect its coz the string is changed to binary then compared with the others which is very slow

c) Use the smallest data type possible - if you expect your table to have very few columns say 52 US states, then use the smallest type possible maybe a CHAR(2) for the 2 digit code, but I would still go for a tinyint (128) for the column vs a big int which can go up to 2billion

Also you will have a challenge with cascading your changes from the primary keys to the other tables if for example the project name changes (which is not uncommon)

Go for sequential auto incrementing integers for your primary keys and gain the inbuilt efficiencies that database systems provide with support for changes in the future

share|improve this answer
    
"joining to varchars vs integers is about 32x slower" Any reference for that? Is that a result from your tests? –  ypercube Apr 1 '12 at 8:34
    
@ypercube sorry was a typo on my end should have been 2x - 3x as the length of the key grows, have updated the answer to clarify that. Thanks for the catch –  ssmusoke Apr 1 '12 at 9:06
1  
Strings are not changed to binary; they're stored in binary from the beginning. How else would they be stored? Perhaps you're thinking of operations to allow case-insensitive comparison? –  Jon of All Trades Apr 2 '12 at 20:32

The biggest downside is repetition of the PK. You pointed out an increase in disk space usage but to be clear the increased index size is your bigger concern. Since innodb is a clustered index every secondary index internally stores a copy of the PK which it uses to ultimately find matching records.

You say are tables are expected to be "small" (20 rows indeed is very tiny). If you have enough RAM to set the innodb_buffer_pool_size equal to

select sum(data_length+index_length) from information_schema.tables where engine='innodb';

Then do that and you'll probably be sitting pretty. As a general rule though you'd want to leave at least 30% - 40% of total system memory for other mysql overhead and dis cache. And that's assuming it's a dedicated DB server. If you have other things running on the system you'll need to take their requirements into consideration as well.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.