Hacker News Reader

For case 2, a good index in a normal RDBMS also gives the same benefit. If your query only touched the columns in the index, the query would be blasting fast. The full rows are not used at all. One trick to improve query performance is to create composite index that contains all the columns needed in the query, which is like a mini-table with only the columns in the query.

View on HN

Yes but each additional index adds to the storage requirement of the data. With column oriented designs each column is often indexed with bitmap indexes which are very small (often the index + raw data is smaller than the raw data stored in an array thanks to compression). Index intersection is also much faster with bitmap indexes rather then b-trees.

One trick to improve query performance is to create composite index that contains all the columns needed in the query, which is like a mini-table with only the columns in the query.

MSSQL also allows you to create an index and include other columns with the index data while leaving them out of the index itself. I don't have time to check if this is standard SQL or an MSSQL extension.

http://msdn.microsoft.com/en-us/library/ms188783.aspx

Reply on news.ycombinator.com