SQL Database Optimization

Discussion in 'Scripts, 3rd Party Apps, and Programming' started by batman1, Jun 19, 2009.

  1. batman1

    batman1 New Member

    Messages:
    92
    Likes Received:
    0
    Trophy Points:
    0
    Should I add and INDEX onto a date "Y-m-d" field, even though many records can have the same date.

    I run a query that check all those records with last login which is today.

    Please reply. :cool:

    Points for helpful help.
     
  2. xav0989

    xav0989 Community Public Relation Community Support

    Messages:
    4,467
    Likes Received:
    95
    Trophy Points:
    0
    I personally don't add any index fields except for the primary field. Unless you have 10 000 rows, it won't make your queries faster. It will even slow them down.
     
  3. misson

    misson Community Paragon Community Support

    Messages:
    2,572
    Likes Received:
    72
    Trophy Points:
    48
    It's rather hard to answer this sort of question given so little information. Please post:
    • the tables' definitions,
    • the indices already defined on the tables ("SHOW INDEX FROM <table>;"),
    • the query you run, and (if it's a SELECT query)
    • the output of "EXPLAIN <query>" (2, 3), where <query> is your select statement. This can also be used to refactor the select statement.
    Keep in mind that indices can speed up selects but will slow down any query that changes the data in the tables (inserts, updates and deletes). Usually you get data from a table more often than you change it, but if this isn't true an index may not help overall.

    Alternatively, you could take the experimental approach. Get the timing from running the query a few thousand/million times without the index, and then with the index. If it's faster without, drop the index.
     
    Last edited: Jun 20, 2009
  4. batman1

    batman1 New Member

    Messages:
    92
    Likes Received:
    0
    Trophy Points:
    0

    your post has been helpful thanks for the reply.
     
  5. xav0989

    xav0989 Community Public Relation Community Support

    Messages:
    4,467
    Likes Received:
    95
    Trophy Points:
    0
    Could you still post the results for the benefit of the community?
     
  6. vol7ron

    vol7ron New Member

    Messages:
    434
    Likes Received:
    0
    Trophy Points:
    0
    Wrong and wrong.

    The more records a table has, the more important indices become and yes they do work against smaller tables because the database first scans the data library and finds the index and then scans the physical table for data. If the original table is small enough, then it's faster to perform one table scan then two, but you can't put a number on how many records this occurs at.

    The second point is wrong because indices are used and help improve performance on Updates and Deletes in addition to Select queries. They are generally not used on Inserts unless a table join or conditional statement exists because Interts just append a new row to the end of a table.

    The important thing to take away is that large tables need indices - I'd put the number starting around 2500 records even though as i said before, there is no real number (especially depending on hardware). But the most important thing to remember is that the only fields that need to be indexed are the ones used in a WHERE clause of a query. Once you set that up, determining what type of index to use (b-tree, etc) is the next step to performance tuning.
     
    Last edited: Jun 21, 2009
  7. misson

    misson Community Paragon Community Support

    Messages:
    2,572
    Likes Received:
    72
    Trophy Points:
    48
    You're right in that indices can help with UPDATEs and DELETEs. However, there is a cost to using indices, including the need to update them, which will negatively impact performance. Generally, the trade-off results in a net gain.
     
  8. xav0989

    xav0989 Community Public Relation Community Support

    Messages:
    4,467
    Likes Received:
    95
    Trophy Points:
    0
    What I like about the internet is that you always learn something. Just now, I've learned that something that I thought was true for a long time. I won't sleep as dumb tonight! (no sarcasm)
     

Share This Page