Mysql indexes: Difference between revisions
From wikinotes
(Created page with "Indexes use various datastructures to store data to prevent full scans of all rows in the table. = Index Types = <blockquote> == BTree == A binary tree. Default for persisted storage engines. Optimize Queries that are * sorted * field prefixes * range (less than 10, between C and F) But not queries * field suffixes == Hash == 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...") |
(→BTree) |
||
| Line 8: | Line 8: | ||
Optimize Queries that are | Optimize Queries that are | ||
* sorted | * sorted | ||
* field prefixes | * match on field prefixes (but not suffixes) | ||
* range (less than 10, between C and F) | * range bound (less than 10, between C and F) | ||
But not queries | But not queries | ||
* match on field suffixes | |||
* field suffixes | |||
== Hash == | == Hash == | ||
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)
