Would an index make this query faster?
-
select cs.customer_name, st.staff_name
from Customer cs
inner join Staff st on cs.city_id=st.city_id(just a sample query, dont ask for its purpose) I'm quite sure that we need an index on st.city_id to speed things up. But would an index on cs.city_id make any different? Because it looks like we have to scan entire Customer table anyway.
-
select cs.customer_name, st.staff_name
from Customer cs
inner join Staff st on cs.city_id=st.city_id(just a sample query, dont ask for its purpose) I'm quite sure that we need an index on st.city_id to speed things up. But would an index on cs.city_id make any different? Because it looks like we have to scan entire Customer table anyway.
Assuming you are using SQL Server, turn on the Profiler in SSMS and run the query, it will then recommend an index if required.
Never underestimate the power of human stupidity RAH
-
select cs.customer_name, st.staff_name
from Customer cs
inner join Staff st on cs.city_id=st.city_id(just a sample query, dont ask for its purpose) I'm quite sure that we need an index on st.city_id to speed things up. But would an index on cs.city_id make any different? Because it looks like we have to scan entire Customer table anyway.
khun_panya wrote:
But would an index on cs.city_id make any different?
Most probably it will. The query optimizer decides whether to use an index or not depending on various criteria. If the number of rows in the table is very less, it might do a table scan rather than an index seek. But this is purely left to the optimizer to decide.
khun_panya wrote:
Because it looks like we have to scan entire Customer table anyway.
What makes you think so? If execution plan shows a table scan, then either an index does not exist or the number of rows is very less to warrant an index seek.
-
select cs.customer_name, st.staff_name
from Customer cs
inner join Staff st on cs.city_id=st.city_id(just a sample query, dont ask for its purpose) I'm quite sure that we need an index on st.city_id to speed things up. But would an index on cs.city_id make any different? Because it looks like we have to scan entire Customer table anyway.
There is a good chance that an index would speed things up. The advice given so far is sound, however.. indexes are a bit of an art and like all arts you have to experiment occasionally. Two areas that affect indexes are the number of rows and the frequency of updates and inserts. For this reason you need to understand some of the theory behind indexes and also be willing to experiment with adding indexes to see what happens and learn from that. I don't have any links but I would suggest giving yourself a good hour to google and read up on indexes as it will stand you in good stead.
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
-
There is a good chance that an index would speed things up. The advice given so far is sound, however.. indexes are a bit of an art and like all arts you have to experiment occasionally. Two areas that affect indexes are the number of rows and the frequency of updates and inserts. For this reason you need to understand some of the theory behind indexes and also be willing to experiment with adding indexes to see what happens and learn from that. I don't have any links but I would suggest giving yourself a good hour to google and read up on indexes as it will stand you in good stead.
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
Here's[^] a really good link about indexes.
Wrong is evil and must be defeated. - Jeff Ello[^]
-
khun_panya wrote:
But would an index on cs.city_id make any different?
Most probably it will. The query optimizer decides whether to use an index or not depending on various criteria. If the number of rows in the table is very less, it might do a table scan rather than an index seek. But this is purely left to the optimizer to decide.
khun_panya wrote:
Because it looks like we have to scan entire Customer table anyway.
What makes you think so? If execution plan shows a table scan, then either an index does not exist or the number of rows is very less to warrant an index seek.
Shameel wrote:
khun_panya wrote:
Because it looks like we have to scan entire Customer table anyway.
What makes you think so? If execution plan shows a table scan, then either an index does not exist or the number of rows is very less to warrant an index seek.
I have not checked for real execution plan. But above query do select with "no filter". So it would have to iterate all the rows in table. That's why I doubt that an index helps in this situation.