I read somewhere in my email trail with local
DBAs that if selected colums in a query are available
in indexed columns, the query is faster.
Any idea how much faster it makes a select query if all selected colums are available in indexed compinent vs some could not available in indexed components
Thanks
Nilesh
Joined: 31 Oct 2006 Posts: 981 Location: Richmond, Virginia
Wouldn't you feel more comfortable with your understanding of this complex issue by relying on your own easily designed testing as opposed to someone you don't know telling you the answer?
You are a new user. I and I assume just about every other experienced programmer/DBA/etc. on this excellent forum encourage you to own your experience and education by investing your time, analytical thinking, and programming skills in gaining knowledge.
Then YOU will become the answer person at your job location (and can then guide your own novices on how to test their own questions!).
I read somewhere in my email trail with local
DBAs that if selected colums in a query are available
in indexed columns, the query is faster.
Any idea how much faster it makes a select query if all selected colums are available in indexed compinent vs some could not available in indexed components
Thanks
Nilesh
1. Do you know if the index is unique/non-unique?
2. How large is the table?
3. Why didn't you ask the same question to your DBA on the same email chain?
'how much faster' depends on point 1 and 2 above (and possibly some more). Suppose your table has only 5 rows then, what do you think would happen if you have an index on your select column? Your query will be slower and Not faster as it is a very small table and a complete table scan will be faster than Index search.
I read somewhere in my email trail with local
DBAs that if selected columns in a query are available
in indexed columns, the query is faster.
Any idea how much faster it makes a select query if all selected columns are available in indexed component vs. some could not available in indexed components
I doubt your DBA said it true everything and even considerable.
If you have unused indexes then the performance will slow down because of too much corresponding sorts and this is implemented by us and saved a lot of $$ every year by taking out unused INDEXes. You would want the better indexes on the columns used as a pert of WHERE clause and you don't/never design indexes based on SELECTed columns in your query.
Moreover, if you have any performance issues then did you try EXPLAIN first? did you make sure all other performance factors checklist(which can be found on internet or IBM site) to make sure your SQL Concur the fact?
