Indexing Question
-
Hi, I can add two different columns to an index. What is the difference if I make two separate indexes for two columns instead of creating one index and adding two columns to it. Thanks
_
-
Hi, I can add two different columns to an index. What is the difference if I make two separate indexes for two columns instead of creating one index and adding two columns to it. Thanks
_
This depends very much on the database you are using (SQL Server, Oracle, MySQL etc). But basically if you create two separate indexes, they can be used independently. When you have one index, the tree traversal must use the first column even though you are not referring to it in a query. Think of it like an index in a book. If you have two levels on chapters like: - 1.1 - 1.2 - 1.3... - 2.1 - 2.2 - 2.3... and you want to find all chapters that are numbered ???.3 you must scan through the whole index to find what you are looking for. Disclaimer: The description is really simplified and is not accurate for all databases so the situation is actually much more complex. But this should give you the idea. Hope this helps, Mika
The need to optimize rises from a bad design
-
This depends very much on the database you are using (SQL Server, Oracle, MySQL etc). But basically if you create two separate indexes, they can be used independently. When you have one index, the tree traversal must use the first column even though you are not referring to it in a query. Think of it like an index in a book. If you have two levels on chapters like: - 1.1 - 1.2 - 1.3... - 2.1 - 2.2 - 2.3... and you want to find all chapters that are numbered ???.3 you must scan through the whole index to find what you are looking for. Disclaimer: The description is really simplified and is not accurate for all databases so the situation is actually much more complex. But this should give you the idea. Hope this helps, Mika
The need to optimize rises from a bad design
Thanks for your response. To be more specific, I'm talking about
SQL Serverdatabase. Suppose that I've got a query like this:select id, fname, lname, age from customers where lname = @lname order by age asc
Besides having a default clustered index on id column, I can have an index containing two columns,
lnameandage. But I can also have an index forlnameand an index foragecolumn separately. Does it matter which one is better in this case? I may also have another query like this:select id, lname from customers order by age asc
for the sake of this query, according to what I learned from your explanation, having an index containing only
agecolumn would be better than an index containinglnameandage. right? Can I have both of aforementioned indexes together? Will SQL Server determine which index is better to use based on my query? Thanks for your help_
-
Thanks for your response. To be more specific, I'm talking about
SQL Serverdatabase. Suppose that I've got a query like this:select id, fname, lname, age from customers where lname = @lname order by age asc
Besides having a default clustered index on id column, I can have an index containing two columns,
lnameandage. But I can also have an index forlnameand an index foragecolumn separately. Does it matter which one is better in this case? I may also have another query like this:select id, lname from customers order by age asc
for the sake of this query, according to what I learned from your explanation, having an index containing only
agecolumn would be better than an index containinglnameandage. right? Can I have both of aforementioned indexes together? Will SQL Server determine which index is better to use based on my query? Thanks for your help_
Having both indexes (lname, age and age) is ok. However this will have some performance penalty for
insert,updateanddeletestatements. You could try having onlylname + ageorlnameandageseparately. The optimizer will pick up the index / indexes it will consider most benefitial. You can verify optimizer behaviour using execution plan. If you have onlylname + age, it is possible that the optimizer makes horizontal scan on the index tree for the second query. If you havelnameandageseparately, the optimizer can choose to make an index join for the first query. So try all variations and use execution plan to see what is reasonable in your case (especially consider that those are hardly the only queries, so you should consider all query needs for this table if possible). It's like playing chess :) MikaThe need to optimize rises from a bad design
-
Having both indexes (lname, age and age) is ok. However this will have some performance penalty for
insert,updateanddeletestatements. You could try having onlylname + ageorlnameandageseparately. The optimizer will pick up the index / indexes it will consider most benefitial. You can verify optimizer behaviour using execution plan. If you have onlylname + age, it is possible that the optimizer makes horizontal scan on the index tree for the second query. If you havelnameandageseparately, the optimizer can choose to make an index join for the first query. So try all variations and use execution plan to see what is reasonable in your case (especially consider that those are hardly the only queries, so you should consider all query needs for this table if possible). It's like playing chess :) MikaThe need to optimize rises from a bad design
Yea it's like playing a chess! :) From what I've seen in execution plan, it tells me that how much percent of time has been spent on which phase of query. Do you have any recommendation for me on how to make best use of execution plan to fine tune my indexes? I'm sorry if this question is very general and might bother you. Thanks again
_
-
Having both indexes (lname, age and age) is ok. However this will have some performance penalty for
insert,updateanddeletestatements. You could try having onlylname + ageorlnameandageseparately. The optimizer will pick up the index / indexes it will consider most benefitial. You can verify optimizer behaviour using execution plan. If you have onlylname + age, it is possible that the optimizer makes horizontal scan on the index tree for the second query. If you havelnameandageseparately, the optimizer can choose to make an index join for the first query. So try all variations and use execution plan to see what is reasonable in your case (especially consider that those are hardly the only queries, so you should consider all query needs for this table if possible). It's like playing chess :) MikaThe need to optimize rises from a bad design
I found an article here: Execution Plans[^] I think it can help me a lot. Thanks again for your help :)
_
-
I found an article here: Execution Plans[^] I think it can help me a lot. Thanks again for your help :)
_
Just noticed your message. That article is a good one. Especially concentrate on the total cost (= estimated seconds) and how it's distributed. Then observe logical I/O amounts (don't mind so much about physical I/O). Your question is not bothering me at all. Actually this area is one of my special interests :) Happy coding! ;) Mika
The need to optimize rises from a bad design