SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,764
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Cost of MySQL Indexes?

    I'm curious what impact Indexes have on the performance of MySQL and ultimately my website...

    For example, what is the "cost" of a Standard Index in MySQL?

    And what is the "cost" of a Unique Index in MySQL?

    And how about the "cost" of a Primary Key/Index?


    For example, I have a table with the following Fields and associated Indexes...

    Code:
    - id (PK)
    - slug (Unique Index)
    - name (Unique Index)
    - created_on
    - updated_on

    I feel guilty having 3 Indexes of a Table with only 5 Fields, but what are you going to do?!

    Sincerely,


    Debbie

  2. #2
    Programming Since 1978 silver trophybronze trophy felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, NSW, Australia
    Posts
    16,807
    Mentioned
    25 Post(s)
    Tagged
    1 Thread(s)
    indexes slow the updating of the content of the data because the indexes need to be updated as well as the content.

    indexes speed up the reading of the data by providing quicker ways of locating what is wanted.

    the cost of having or not having an index depends on the ratio of the time saving for reads that are speeded up by it compared to the extra time required by updates that are slowed down by it.
    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="^$">

  3. #3
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,764
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by felgall View Post
    indexes slow the updating of the content of the data because the indexes need to be updated as well as the content.

    indexes speed up the reading of the data by providing quicker ways of locating what is wanted.

    the cost of having or not having an index depends on the ratio of the time saving for reads that are speeded up by it compared to the extra time required by updates that are slowed down by it.
    Well, the table I described is really a "lookup" table, so it will rarely get updated.


    Debbie

  4. #4
    Programming Since 1978 silver trophybronze trophy felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, NSW, Australia
    Posts
    16,807
    Mentioned
    25 Post(s)
    Tagged
    1 Thread(s)
    Quote Originally Posted by DoubleDee View Post
    Well, the table I described is really a "lookup" table, so it will rarely get updated.
    So whatever indexes you add that will speed up those lookups will be making things faster. You only need the ones that are actually going to get used though.
    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="^$">

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,247
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by DoubleDee View Post
    I feel guilty having 3 Indexes of a Table with only 5 Fields, but what are you going to do?!
    if slug and name are supposed to be unique, you aren't going to touch them at all

    imagine ensuring their uniqueness in some other way -- you'll end up doing one or two SELECTs before the INSERT, to see if the values are already in the table, and then of course you'll have to wrap them in a transaction to prevent race conditions, etc.

    you ~could~ get rid of the id, and use either slug or name as the PK
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •