Note: This is from an introductory database systems course at my university so I'm not entirely sure if it matters, but for what it's worth we are being taught MySQL.
One homework question I'm doing right now is to analyze some query plans for a query with two predicates, specifically:
SELECT *
FROM Student
WHERE wam > 75 AND faculty = 'Arts';
however the plans provided assume an index only on one of the predicates, such as an unclustered B+ tree index on (wam).
One example part of the question is as follows:
Compute the estimated cost of the best plan assuming that an unclustered B+ tree index on (wam) is the only index available. Suppose there are 60 index pages. Discuss and calculate alternative plans
How would I go about calculating the cost of a plan like this? Would I have to calculate costs for unclustered/clustered B+ tree and hash indexes for the other predicate, faculty, as well? I am aware that one alternative plan is also simply a full table scan.
"Student" has 800 pages, 64000 tuples. "faculty" can take 10 unique values. "wam" can take 100 unique values.
Thank you, if I'm not being specific enough feel free to point out anything lacking in this post.