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:D?
Ofcourse primary key itself having index, so we need to keep 4.
please reply for the above doubt.
Regards,
Sunil.(:
wrong, sorry
when you use LIKE with a wildcard at the front of the string, you will get a table scan
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.
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/Coding-and-Indexing-Strategies-for-Optimal-Performance
And I also recommend reading “Relational database index design and the optimizers” for more indepth understanding of indexing