Count the costs of operations and find an optimal selection of indexes for the P
ID: 3572044 • Letter: C
Question
Count the costs of operations and find an optimal selection of indexes for the Product table (Hardware Database) under the following assumptions:
Operations
Query Q1 (20%): SELECT * FROM Product WHERE model=X
Query Q2 (10%): SELECT * FROM Product WHERE maker=Y AND type=Z
Insertion I (70%): INSERT INTO Product VALUES (A,B,C)
Assumption about data (the same as on the slides)
Data for the Product table is stored in 10 blocks.
The Model attribute is a key for Product.
On the average, a maker produces 3 models of each type.
Records are spread uniformly over the 10 blocks: On the average, 3 records with the same maker are stored in 3 blocks.
Any index for Product is stored in 1 block.
Four cases to analyze (the same as on the slides)
1.No indexes for Product.
2.Index on Model only.
3.Index on (Maker,Type) only.
4.Index on Model and index on (Maker,Type).
Explanation / Answer
If you are using SQL SERVER then not to worry about taking an Index on your Primary Key as it always indexed by sql server, else You need to put an Index on Primary Key first. Indexing helps to fetch data faster but it will increase the cost of Insert operation. If you have maximum of Insertion operations then it will not that much helpful to put Indexes on other two columns.
So Index on PK will do it all..