Mysql int vs varchar as primary key (InnoDB Storage Engine

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.