Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2011
    Posts
    32

    Unanswered: Indexing a URL column (Unique Index, Regular Index, Reversed URLs)

    Hi,

    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?


    Appreciated...


    .

  2. #2
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    It depends how you are going to access the member name and/or random url name?
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  3. #3
    Join Date
    Jul 2011
    Posts
    32
    The complete URL will be retrieved from this table not the member_name i.e.

    WHERE url='member-name/contact-info'

  4. #4
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    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.
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  5. #5
    Join Date
    Jul 2011
    Posts
    32
    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.


    Appreciated...
    Last edited by akrashdi; 10-24-12 at 15:08. Reason: New experiments

  6. #6
    Join Date
    Mar 2004
    Posts
    480
    Both of your solutions are likely slower than splitting into two columns as suggested.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •