If you look at the URL structure for this question /questions/question-id/question-slug it's obvious that SO is using question-id for the database lookup. The slug is only there for SEO and informational purposes.

But in the tags lookup, we get a structure like /tagged/tag-slug

I'm wondering why SO would use the question ID to do a lookup but not the a tag ID, instead of using the tag-slug.

I'm wondering if I'm missing a trick.


There's a much smaller, and more importantly unique set of tags, so it's possible to use them to retrieve the correct id. Question titles may not be unique and may change multiple times so they can't be relied upon.

| improve this answer | |

When searching against a database table, you create an index on the column that you are looking up against to find the rows that you are looking for. You want that index to be as small as possible so that you can find the records as quickly as possible taking up as little memory for the index as possible. An index on the QuestionId column will be a much smaller index than that on the question title. Then there's the problem that question titles do not need to be unique, if they did that would present a problem pretty quickly.

Now theory aside lets that practical implementation for a minute. Odds are the QuestionId column is the primary key and the primary key is probably the clustered index on the table. In order to use the question name to do the lookup (ignoring the duplicate value issue I already talked about) a non-clustered index would need to be created on the QuestionTitle column. Now when the SQL Server does the look up against that QuestionTitle column the index would either need to already have all the other values that were needed (QuestionId which it would have as the QuestionId is the primary key, the question body, etc) or the SQL Server would then need to go to the primary key to get these values which is called a Key Lookup which is an expensive operation as another lookup against the Clustered Index needs to be done which then requires another IO to the disk to be done.

When looking up against the Clustered Index all the values which are needed are already stored there in the data page, so the SQL Server doesn't need to do any additional IO to find the needed values.

I'm sure that at this point I've gone about 1000x more in depth than anyone here wants to read, but that's what happens when you let a DBA onto a developer forum. :)

| improve this answer | |
  • Great answer, thanks. I've changed my app to take your points into account. I'd choose yours as the correct answer but the question was migrated from SO and I don't seem to be able to find the tick. Yes, I'm logged in. – mos fetish Dec 19 '10 at 21:54

Probably because it looks nicer, and tag sets are presumably much faster to look up using some sort of static table because there is a limited number of tags.

Just guessing, but it looks like the closest explanation to me.

| improve this answer | |

It is possible that they cache the tag-slug in a dictionary with the tag-id. It probably wouldn't take up much memory to cache tag-slugs, but it would not be feasible for question-slugs. More importantly, the tag-slug is guaranteed to be unique, whereas the question-slug is probably not guaranteed to be unique, thus the requirement for an id.

| improve this answer | |

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .