View previous topic :: View next topic
|
Author |
Message |
Nileshkul
New User
Joined: 09 May 2016 Posts: 43 Location: India
|
|
|
|
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 |
|
Back to top |
|
|
Phrzby Phil
Senior Member
Joined: 31 Oct 2006 Posts: 1042 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!). |
|
Back to top |
|
|
RahulG31
Active User
Joined: 20 Dec 2014 Posts: 446 Location: USA
|
|
|
|
Nileshkul wrote: |
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.
. |
|
Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010 Posts: 3049 Location: NYC,USA
|
|
|
|
Quote: |
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? |
|
Back to top |
|
|
|