SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Enthusiast
    Join Date
    Dec 2009
    Posts
    53
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Need clarity on INDEX

    Dear consultant,

    All are saying that if we keep INDEX for columns, it will increase the performance.

    Suppose take one table having 5 columns. For our application we will use all columns for searching. (Means column1 like '%xyz%' or column2 like '%xyz%'......)

    So, for this we need to give for all columns INDEX to improve performance right?

    Ofcourse primary key itself having index, so we need to keep 4.

    please reply for the above doubt.

    Regards,
    Sunil.

  2. #2
    SitePoint Enthusiast erangalp's Avatar
    Join Date
    Oct 2010
    Location
    Israel
    Posts
    37
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Blindly adding indexes might help performance but only by accident. You would probably do more harm than good. Proper indexing strategy calls for examining your queries using EXPLAIN or deeper inspection tools and testing the effect of indexes on performance or query plan.

    Have a look at this excellent presentation by Jay Pipes - http://www.scribd.com/doc/2376115/Co...al-Performance

    And I also recommend reading "Relational database index design and the optimizers" for more indepth understanding of indexing

  3. #3
    Programming Since 1978 silver trophybronze trophy felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, NSW, Australia
    Posts
    16,862
    Mentioned
    25 Post(s)
    Tagged
    1 Thread(s)
    Quote Originally Posted by nagasunil View Post
    All are saying that if we keep INDEX for columns, it will increase the performance.
    or decrease performance if you add an index on the wrong column.

    Each index you add makes any updates to the site run slower because there's an extra index to update along with the data. Only where having the index allows reads of the data to run a lot faster is the index going to improve overall performance.

    A common cause of performance issues with databases where there are lots of updates is having too many indexes.
    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="^$">

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,336
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by nagasunil View Post
    Suppose take one table having 5 columns. For our application we will use all columns for searching. (Means column1 like '%xyz%' or column2 like '%xyz%'......)

    So, for this we need to give for all columns INDEX to improve performance right?
    wrong, sorry

    when you use LIKE with a wildcard at the front of the string, you will get a table scan
    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
  •