I'm allowing sub-sites on my domain for which I'm storing their URLs (without host+domain names), the allowed length is 100. What's the best way of creating index on this type of string column?
I have read that URLs should be stored reversed as the first part of the url would be the same i.e. member_name/random-url-name. So, should I reverse the URL and create index on first (20-30) characters?
Or I should just create Unique index on the whole column?
Or should I check uniqueness with the code and create a regular index?
why not store the member name in one field and the contact-info in a separate field. Then your query would be WHERE member_name = 'member-name' AND contact_info = 'contact-info'. There would be no need to reverse index anything in this case.
That's right no need to reverse index in this case. Even if I keep it the way it is, indexing first 25 characters will work fine since the first part of the url is member-name, so indexing will keep member's all URLs sorted (in the same place).
I don't see any other advantage of splitting fields into two, because I already have primary-foreign keys in 'member' and 'pages' tables to identify urls that belong to any particular member.
So, how much difference there would be in terms of performance if I do one of the following:
Unique Index for the URL column varchar(100)
Index on first 25 chars of the URL and check uniqueness with the code.
Last edited by akrashdi; 10-24-12 at 15:08.
Reason: New experiments