I am building a large-ish site that has many content-managed sections. It all needs to be searchable so I am having a single
pages tables that has a FULLTEXT index on it for searches.
Each pages has a unique URL-slug that is used for URL rewriting.
I am using the section followed by the ID so on two sections you have the same page name.
…would both be valid. The above would have a unique ID of news/test-post and recipes/test-post respectively. I have three questions regarding indices:
- Is there any point having auto-increment int as the primary key when it already has a unique ID? I am guess no in the front end as lookups will always be done by the slug but perhaps in the CMS as they may use the int instead. Either way, my gut says the extra index is not worth the overhead.
- Is there a big performance difference between having a string of say 50 chars as a primary key compared to auto-increment int?
- Would there be any performance increase or decrease if I split the slug into two parts and set as a compound (?) index? So in the above examples news and test-post and recipes and test-post would be in different fields/columns. I am guess not as I will generally be selecting by primary key or FULLTEXT search.