Re: Using multiple indices on a single table in a query
From: Andrew J. Kelly (sqlmvpnooospam_at_shadhawk.com)
Date: 09/17/04
- Next message: charles: "Thanks for the suggestions"
- Previous message: Ganesan Rajaraman: "Re: Weird Cast operator"
- In reply to: Jami Bradley: "Using multiple indices on a single table in a query"
- Next in thread: Jami Bradley: "Re: Using multiple indices on a single table in a query"
- Reply: Jami Bradley: "Re: Using multiple indices on a single table in a query"
- Messages sorted by: [ date ] [ thread ]
Date: Fri, 17 Sep 2004 15:36:35 -0400
1. Actually the caching is not done on a row by row basis. Everything in
memory and disk pretty much happens at he page or extent level. You will
never read a single row into memory unless there is only 1 row on a page. I
am not 100% sure where you are going with this but it is not necessary to
index every column or keep every row in cache. If you do something once a
day like a table scan for a distinct it is usually not worth keeping all
that data in cache at the expense of more frequently used data.
2. SQL Server is quite capable of using two indexes to find a common row.
This is called index intersection. But the conditions must be correct for
this to be more useful than the example you listed. Just because it didn't
use index intersection in your simple test does not mean it is not capable
of doing so. If the first index is selective enough (and especially if you
use * for the column list) the chances are it is less effort to do the
bookmark lookup and filter out the unwanted rows.
-- Andrew J. Kelly SQL MVP "Jami Bradley" <jbradley@isa-og.com> wrote in message news:madmk0tvupsp07qcu14ioq0clj2l612pfg@4ax.com... > Hi, > > Over the past several months I have been working on our schema (extending and studying > performance). Two interesting thoughts on performance have come up: > > 1. AFAIK, caching of tables is done on a row by row basis. It would be interesting to cache by > column. Where this comes in handy is when certain commands are executed infrequently (and they are > not indexed). For example, SELECT DISTINCT X FROM Y. Of course, indexing would dramatically help > this query, but sometimes the columns just are worth indexing. Now, if the entire column were > cached (the table is too large to cache in entirety), this would still be pretty quick. > > Actually, a single column index is much like a column cache. I just don't feel like indexing every > column individually :-) > > > 2. Why don't queries make use of multiple indices on a single table in a query? > For example, SELECT * FROM T WHERE a = aVal and b = bVal > I could make an index on (a,b) which would work great for this query. But, what if I had an index > on (a) and a second index on (b). You could use the intersection of the two subsets and grab the > rows from that list. Instead what I see is an index seek on the more selective index followed by a > bookmark lookup to get the other column in the where clause. > > I can see why #1 might not be implemented - it is complex and a single column index addresses most > of it anyway. But for #2, it seems like it would be really helpful in a lot of cases. Any other > reasons why #2 isn't done? > > Thanks, > > Jami >
- Next message: charles: "Thanks for the suggestions"
- Previous message: Ganesan Rajaraman: "Re: Weird Cast operator"
- In reply to: Jami Bradley: "Using multiple indices on a single table in a query"
- Next in thread: Jami Bradley: "Re: Using multiple indices on a single table in a query"
- Reply: Jami Bradley: "Re: Using multiple indices on a single table in a query"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
