In a query where multiple indices can be used, it will pick the most selective one (AKA the one with the closes ratio to 1 of distinct values/ total rows. The primary key will always have a perfect ratio of 1, hence why it’s the best index.
The index will keep a heuristic (close approximation) of the distinct values for quick access, called cardinality.
If an index has really low selectivity for a specific query, the RDBMS will forego using it altogether and read the table instead.
They allow indexing very large values in a more efficient manner (albeit at the cost of potential collisions).
You can pick how many characters to index of a certain column.
A composite index takes into account the order of the columns chosen
<aside> 💡 The rules are:
</aside>
Left to right, no skipping → If you create an index on columns A, B and C, you can use that index on a query that searches only in A, but not for one that searches in B or C, as they require searching for a value in A first. Even if you use column A and C in your query, only column A will be checked against the index because B isn’t included as well.
Stop at the first range → The moment one of the columns is checked for a range (≤, <, >, ≥, etc) then the rest of the columns will not be checked against the index.
The general rule of thumb is to create the index with the most selective columns first, but always tailor it to access patterns!
We say an index is covering when it can fulfil an entire query all by itself, without having to reference back to the original table/index for more data.
They are not a separate type per sé, but rather a quality any given index may have for a query.
The moment one of the columns in the index is placed inside a function, the index cannot be used for it, as it will have been transformed and thus, obfuscated.
2 ways to reduce complexity:
It's easier to tell whether a design is simple than it is to create one. Use the ability to recognise complexity to stay away from it. You’ll start recognising patterns for simplifying designs.