SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    SitePoint Evangelist
    Join Date
    May 2006
    Posts
    424
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Auto increment and slug-based primary key

    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.

    E.g.

    www.domain.com/news/test-post/
    www.domain.com/recipes/test-post/

    …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:

    1. 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.
    2. Is there a big performance difference between having a string of say 50 chars as a primary key compared to auto-increment int?
    3. 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.

    Thanks.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,020
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    my opinion...

    1. no
    2. depends how you define "big"
    3. no
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Evangelist
    Join Date
    May 2006
    Posts
    424
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Thanks, I thought as much re points 1 and 3.

    Regarding point two. Let's say you have 10,000 records and SELECT * FROM `table` WHERE id = '$id' takes 100 milliseconds with a 50 varchar as the PK, do you think with auto_increment it would be a couple of milliseconds quicker or say twice as fast? I guess I should set up a test table and find out for myself but if you have any experience, feel free to share it. Based on how I understand integer based indices work I would guess that ints are quite a bit faster.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,020
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by DrQuincy View Post
    ...I would guess that ints are quite a bit faster.
    depends on how you define "quite a bit"

    time for some testing, yes?
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Evangelist
    Join Date
    May 2006
    Posts
    424
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    LOL, yep.

  6. #6
    SitePoint Wizard bronze trophy Jeff Mott's Avatar
    Join Date
    Jul 2009
    Posts
    1,154
    Mentioned
    14 Post(s)
    Tagged
    0 Thread(s)
    Also consider whether your URL slugs might ever change, because the unique ID that you use as a foreign key in other tables must not change, otherwise those relations will break. (It's also harder to use compound values as a foreign key.) My preference, for simplicity, keep an int ID.
    "First make it work. Then make it better."

  7. #7
    Programming Since 1978 silver trophybronze trophy felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, NSW, Australia
    Posts
    16,607
    Mentioned
    24 Post(s)
    Tagged
    1 Thread(s)
    Quote Originally Posted by Jeff Mott View Post
    Also consider whether your URL slugs might ever change, because the unique ID that you use as a foreign key in other tables must not change, otherwise those relations will break. (It's also harder to use compound values as a foreign key.) My preference, for simplicity, keep an int ID.
    Of course if the slug is guaranteed to never change then adding the int id just makes for added complexity and should only be included if the added efficiency for lookups is sufficient to outweigh the added complexity that having two unique keys adds to the code.

    Generally not using int ids where there is another field suited to use as the key is the simpler solution.
    Stephen J Chapman

    javascriptexample.net, Book Reviews, follow me on Twitter
    HTML Help, CSS Help, JavaScript Help, PHP/mySQL Help, blog
    <input name="html5" type="text" required pattern="^$">

  8. #8
    SitePoint Wizard bronze trophy Jeff Mott's Avatar
    Join Date
    Jul 2009
    Posts
    1,154
    Mentioned
    14 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by felgall View Post
    Of course if the slug is guaranteed to never change...
    Even then, I'm still iffy about it. If it's a public-facing value, then no guarentee is as strong as you might think. Tomorrow, the boss/client could say, "I've changed my mind. I want people to be able to change their URLs." Then you'd be wishing you had used an int ID from the beginning.
    "First make it work. Then make it better."

  9. #9
    SitePoint Evangelist
    Join Date
    May 2006
    Posts
    424
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Thanks for the replies. Jeff has an interesting point and in this particular implementation the URLs can change—though they won't very often. However, because I need a FULLTEXT index, it's a MyISAM table so any relation between other tables would have to be done manually anyway. (I'm assuming that's right, as you can tell, databases aren't my strong point)

    That's prompted another question regarding IDs and slugs but I think it would be more appropriate in another thread.


Bookmarks

Posting Permissions

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