Indexing Flashcards
How to score an index?
Three star method
★ Puts relevant rows next to each other: “This minimizes the thickness of the index slice that must be scanned.”
★ Index is sorted in the order the query is looking for.
★ Index contains all columns needed by the query (covering).
Downsides to indexing
○ indexes take up extra space
○ Indexes speed up SELECT but slow down INSERT, UPDATE, and DELETE, since changes to a table will require that all indexes covering affected data will also be modified.
○ To earn the third star for an index we need to include every column used in the SELECT. This may create a very large index.
Approach to selecting an index?
Find Cardinality
Find Selectivity
Score index
How do you find index cardinality?
The cardinality of an index is the number of unique values that it actually contains.
This can be used to work out how effective an index might be for a query
Describe index selectivity
Index selectivity is the ratio of the number of distinct indexed values (the cardinality) to the total number of rows in the table (#T), and ranges from 1/#T to 1.
A highly selective index is goodbecause it lets MySQL filter out more rows when it looks for matches. A unique index has a selectivity of 1, which is as good as it gets.
What is a primary index?
A primary index is an index on a set of fields that includes the unique primary key for the field and is guaranteed not to contain duplicates. Also Called a Clustered index. eg. Employee ID can be Example of it.
What is a secondary index?
A Secondary index is an index that is not a primary index and may have duplicates. eg. Employee name can be example of it. Because Employee name can have similar values.
What is a multi-column index?
An index that combines multiple column index values into one column.
What is a prefix index?
Combines index columns with a prefix of long string indexes e.g. 12blac is a prefix index of product id and item name.
