MySQL Indexing
Dr.Nikorn Rongbutsri
Introduction to MySQL Indexing
In MySQL, indexing is a crucial aspect of database optimization, aiming to
enhance the speed and efficiency of query processing. MySQL supports various
indexing techniques, each designed to address specific use cases and query
patterns. Understanding how indexing works in MySQL is essential for creating
well-performing databases.
Syntax for Creating Indexes in MySQL
Creating indexes in MySQL is a straightforward process using the CREATE
INDEX statement or incorporating index definitions within the CREATE TABLE
statement. 3 ways to create index in MySQL:
1. CREATE INDEX
CREATE INDEX Indexname ON Tablename (indexcolumn1, indexcolumn2,
….);
2. CREATE TABLE
CREATE TABLE Tablename (column1 CHAR (30) NOT NULL, INDEX
(column2));
3. ALTER TABLE
ALTER TABLE Tablename ADD INDEX (column1, column2);
Index Types
● B-tree
● Unique
● Primary key
● Full text
● Spatial
● Hash* (not supported by MySQL)
World Database
sample structure
B-Tree Index
● Default indexing mechanism in MySQL.
● Suitable for most scenarios, including exact matches and range queries.
● Automatically created for primary key and unique key columns.
Examples
CREATE INDEX idx_Population ON city (Population);
CREATE INDEX idx_SurfaceArea ON country (SurfaceArea);
CREATE INDEX idx_CountryCode ON countrylanguage (CountryCode);
SRC: slideshare.net
Unique Index
● Enforces the uniqueness of values in a specified column or columns.
● Helps maintain data integrity by preventing duplicate entries.
Examples
CREATE UNIQUE INDEX idx_ID ON city (ID);
CREATE UNIQUE INDEX idx_Code ON country (Code);
CREATE UNIQUE INDEX idx_CountryCode_Language ON countrylanguage
(CountryCode, Language);
Primary Key Index
● Similar to a unique index but has the additional constraint of not allowing
NULL values.
● Often used to uniquely identify each record in a table.
Examples
ALTER TABLE city ADD PRIMARY KEY (ID);
ALTER TABLE country ADD PRIMARY KEY (Code);
ALTER TABLE countrylanguage ADD PRIMARY KEY (CountryCode,
Language);
Full text Index
● Full-text indexes are specifically designed for enhancing full-text searches.
● They are particularly useful when searching for specific words, combinations of
words, or substrings within larger blocks of text, such as blobs.
● Widely implemented in search engines and e-commerce platforms, full-text
indexes are considered vital for efficient operations.
● These indexes are maintained for MyISAM and InnoDB tables in the database.
● Full-text indexes can be applied to columns of type VARCHAR, CHAR, and TEXT.
Examples
ALTER TABLE city ADD FULLTEXT INDEX idx_Name_FullText (Name);
ALTER TABLE country ADD FULLTEXT INDEX idx_Name_LocalName_FullText (Name,
LocalName);
ALTER TABLE countrylanguage ADD FULLTEXT INDEX idx_Language_FullText (Language);
Spatial Index
● Designed for spatial data types, supporting operations on geometries and
geographic coordinates.
● Suitable for applications dealing with location-based data.
Examples
ALTER TABLE country ADD SPATIAL INDEX idx_Geometry_Spatial
(Geometry);
ALTER TABLE city ADD SPATIAL INDEX idx_Location_Spatial (Location);
Hash Index
● Hash Function: Utilizes a hash function to transform keys into hash codes.
● Direct Mapping: Hash codes are used to directly map keys to specific
locations in the index.
● Equality Searches: Well-suited for equality searches, where an exact match
of a key is sought.
● Constant-Time Lookups: Provides constant-time lookups for exact matches,
resulting in efficient query performance.
● Order Preservation: Does not preserve the order of keys, making it less
suitable for range queries.
● Data Types: Applicable to data types that can be hashed, such as integers or
fixed-length strings.
● MySQL not supported
SRC: slideserve.com
Hash index advantages
Efficiency: Offers fast lookup times for exact match queries, especially when
dealing with a large dataset.
Constant-Time Complexity: Provides constant-time complexity for search
operations, making it highly efficient for certain use cases.
Well-suited for Equality Searches: Ideal for scenarios where equality searches
are the primary focus, such as finding exact matches.
Hash index disadvantages
No Range Queries: Less effective for range queries or searches that require
ordering of results.
Hash Collisions: Susceptible to hash collisions, where different keys produce the
same hash code, potentially impacting performance.
Data Type Limitations: May not be suitable for variable-length strings or data
types with varying lengths.
Order Preservation: Lacks order preservation, making it unsuitable for queries
that depend on the order of keys.
Limited Database Support: Not universally supported across all database
systems; some databases primarily use B-tree indexes.
Index Description Use Case Pros Cons
Type
B-Tree Standard index structure in most General indexing, equality and Efficient for ordered data May not perform well for certain types
RDBMS range queries of queries
Unique Enforces uniqueness on indexed Enforcing unique constraints Guarantees uniqueness May slow down INSERTs and
columns UPDATEs
Primary Unique identifier for each record Identifying records Enforces uniqueness and NOT NULL Imposes a structure on the data
Key in a table constraints
Full Text Indexing for full-text search Searching within large text fields Enables efficient searching for words Typically resource-intensive
capabilities and phrases
Spatial Indexing for spatial/geometric Geographical and geometric data Efficient for spatial queries Requires specialized spatial data types
data types
Hash Uses a hash function to index Equality searches Uniform distribution of keys Not suitable for range queries or
data sorting operations
Characteristic B-tree Index Hash Index
Structure Tree structure with nodes and leaf Direct mapping using a hash function.
nodes.
Search Efficient for equality and range queries. Efficient for equality searches, less effective for
Operations range queries.
Order Preserves order of keys, suitable for Does not preserve order, less suitable for
Preservation sorting. sorting.
Database Widely supported in various databases Support varies among databases; not
Support (e.g., MySQL, PostgreSQL, Oracle). universally available.
Data Types Applicable to a variety of data types. Applicable to data types that can be hashed
(e.g., integers, fixed-length strings).
Performance Generally provides good performance Provides constant-time lookups for exact
for a broad range of query types. matches, efficient for specific use cases.
Use Cases Well-suited for mixed equality and Well-suited for scenarios where exact match
range queries. searches are prevalent.
Best Practices and Considerations
1. Choose Appropriate Columns:
● Identify columns frequently used in WHERE clauses or JOIN conditions.
● Consider the cardinality of columns when deciding on index creation.
2. Avoid Overindexing:
● While indexes enhance read performance, they can impact write performance.
● Overindexing (creating too many indexes) can lead to increased maintenance overhead.
3. Regularly Monitor and Optimize:
● Periodically analyze query execution plans and index usage.
● Adjust index strategies based on evolving usage patterns.
Thank you
