Mysql indexes: Difference between revisions
From wikinotes
(→BTree) |
|||
| Line 4: | Line 4: | ||
<blockquote> | <blockquote> | ||
== BTree == | == BTree == | ||
<blockquote> | |||
A binary tree. Default for persisted storage engines. | A binary tree. Default for persisted storage engines. | ||
| Line 15: | Line 15: | ||
But not queries | But not queries | ||
* match on field suffixes | * match on field suffixes | ||
</blockquote><!-- BTree --> | |||
== Hash == | == Hash == | ||
<blockquote> | |||
A hash table. Default for the memory engine.<br> | A hash table. Default for the memory engine.<br> | ||
hash-collisions are accounted for, but each key with the same hash will need to be checked, | hash-collisions are accounted for, but each key with the same hash will need to be checked, | ||
| Line 23: | Line 25: | ||
Optimize queries that are | Optimize queries that are | ||
* based on the full value (ex. <code>IN, NOT IN, =</code>) | * based on the full value (ex. <code>IN, NOT IN, =</code>) | ||
But not queries | But not queries | ||
* you can't match on only a single key from a multi-key index (since both keys are hashed together) | * you can't match on only a single key from a multi-key index (since both keys are hashed together) | ||
* hash-tables are un-ordered, so range queries are not optimized (ex. less than 10, betweeen C and F) | * hash-tables are un-ordered, so range queries are not optimized (ex. less than 10, betweeen C and F) | ||
</blockquote><!-- Hash --> | |||
</blockquote><!-- Index Types --> | </blockquote><!-- Index Types --> | ||
Revision as of 20:11, 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 suffixes
Hash
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
- you can't match on only a single key from a multi-key index (since both keys are hashed together)
- hash-tables are un-ordered, so range queries are not optimized (ex. less than 10, betweeen C and F)
