Mysql indexes: Difference between revisions
From wikinotes
(→Hash) |
(→Hash) |
||
Line 32: | Line 32: | ||
</syntaxhighlight> | </syntaxhighlight> | ||
But not queries | But not queries that | ||
<syntaxhighlight lang="yaml"> | <syntaxhighlight lang="yaml"> | ||
- | - match on only a single key from a multi-key index | ||
- hash-tables are un-ordered, so range queries are not optimized | (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) | |||
</syntaxhighlight> | </syntaxhighlight> | ||
</blockquote><!-- Hash --> | </blockquote><!-- Hash --> | ||
</blockquote><!-- Index Types --> | </blockquote><!-- Index Types --> |
Revision as of 20:14, 4 September 2022
Indexes use various datastructures to store data to prevent full scans of all rows in the table.
Index Types
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)