Mysql indexes: Difference between revisions
From wikinotes
Line 2: | Line 2: | ||
= Index Types = | = Index Types = | ||
<blockquote> | |||
== Index == | |||
<blockquote> | |||
A regular index, on a single column. Defaults to BTREE. | |||
</blockquote><!-- Index --> | |||
== Multi-Column Index == | |||
<blockquote> | |||
A multi-column index is a regular index,<br> | |||
where the values of multiple columns are concatenated together to form one string. | |||
The order of the columns is significant,<br> | |||
but there are debates over the optimal ordering. | |||
<syntaxhighlight lang="yaml"> | |||
- (specific -> unspecfic) will create a larger (on disk) tree, | |||
since there will be duplicates of the unspecific entries | |||
- (unspecific -> specific) TBD | |||
</syntaxhighlight> | |||
</blockquote><!-- Multi-Column Index --> | |||
</blockquote><!-- Index Types --> | |||
= Index Datastructure Types = | |||
<blockquote> | <blockquote> | ||
Show the current index using | Show the current index using | ||
Line 45: | Line 68: | ||
</syntaxhighlight> | </syntaxhighlight> | ||
</blockquote><!-- Hash --> | </blockquote><!-- Hash --> | ||
</blockquote><!-- Index Types --> | </blockquote><!-- Index Datastructure Types --> |
Revision as of 20:43, 4 September 2022
Indexes use various datastructures to store data to prevent full scans of all rows in the table.
Index Types
Index
A regular index, on a single column. Defaults to BTREE.
Multi-Column Index
A multi-column index is a regular index,
where the values of multiple columns are concatenated together to form one string.The order of the columns is significant,
but there are debates over the optimal ordering.- (specific -> unspecfic) will create a larger (on disk) tree, since there will be duplicates of the unspecific entries - (unspecific -> specific) TBD
Index Datastructure Types
Show the current index using
SHOW INDEX FROM foo_table;BTree
A binary tree. Default for persisted storage engines.
Optimize Queries that are
- sorted - match on field prefixes (but not suffixes) - range bound (less than 10, between C and F)But not queries
- match on field suffixesHash
A hash table. Default for the memory engine.
hash-collisions are accounted for, but each key with the same hash will need to be checked, making the query more expensive.
Indexes have a small memory footprint.Optimize queries that are
- based on the full value (ex. 'IN, NOT IN, =')But not queries that
- match on only a single key from a multi-key index (since both keys are hashed together) - are range bound. (ex. less than 10, betweeen C and F) (hash-tables are un-ordered, so range queries are not optimized)