Please enable JavaScript.
Coggle requires JavaScript to display documents.
Indexes Basics (Indexing Strategies for High Performance (Multicolumn…
Indexes Basics
-
Types of Indexes
B-Tree Indexes
The general idea of a B-Tree is that all the values are stored in order, and each leaf page is the same distance from the root
-
B-Tree indexes can normally support index-only queries, which are queries that access only the index, not the row storage.
match the full value; match a leftmost prefix; match a column prefix; match a range of values; match one part exactly and match a range on another part; index-only queries.
they are not useful if the lookup does not start from the leftmost side of the indexed columns; you can’t skip columns in the index; the storage engine can’t optimize accesses with any columns to the right of the first range condition.
Spatial (R-Tree) Indexes
-
-
They index the data by all dimensions at the same time. As a result, lookups can use any combination of dimensions efficiently
Hash Indexes
For each row, the storage engine computes a hash code of the indexed columns, which is a small value that will probably differ from the hash codes computed for other rows with different key values. It stores the hash codes in the index and stores a pointer to each row in a hash table.
A hash index is built on a hash table and is useful only for exact lookups that use every column in the index
If multiple values have the same hash code, the index will store their row pointers in the same hash table entry, using a linked list.
because the index contains only hash codes and row pointers rather than the values themselves, MySQL can’t use the values in the index to avoid reading the rows;
-
hash indexes don’t support partial key matching, because they compute the hash from the entire indexed value. That is, if you have an index on (A,B) and your query’s WHERE clause refers only to A, the index won’t help;
hash indexes support only equality comparisons that use the =, IN(), and <=> operators. They can’t speed up range queries, such as WHERE price > 100;
accessing data in a hash index is very quick, unless there are many collisions (mul-tiple values with the same hash). When there are collisions, the storage engine must follow each row pointer in the linked list and compare their values to the lookup value to find the right row(s);
-
Full-text indexes
a special type of index that finds keywords in the text instead of comparing values directly to the values in the index
Having a full-text index on a column does not eliminate the value of a B-Tree index on the same column. Full-text indexes are for MATCH AGAINST operations, not ordinary WHERE clause operations.
Indexes
Indexes allow the SQL Server engine to perform fast, targeted data retrieval rather than simply scanning though the entire table
Indexes are implemented in the storage engine layer, not the server layer
-