Full Value Matching

When all columns in a composite index are specified with exact values, the index can be fully utilized.

CREATE TABLE vendor_data (
    vendor_id VARCHAR(100) PRIMARY KEY,
    vendor_name VARCHAR(100),
    vendor_alias VARCHAR(50),
    vendor_password VARCHAR(60),
    vendor_status VARCHAR(1),
    vendor_location VARCHAR(100),
    created_date DATETIME
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE INDEX idx_vendor_name_status_location ON vendor_data(vendor_name, vendor_status, vendor_location);

EXPLAIN SELECT * FROM vendor_data 
WHERE vendor_name='Xiaomi Technology' 
AND vendor_status='1' 
AND vendor_location='Beijing'\G;

The query execution plan shows ref as the access type, indicating index usage.

Leftmost Prefix Rule for Composite Indexes

Composite indexes must be used starting from the leftmost column in the index definition.

-- Valid: Uses all index columns
EXPLAIN SELECT * FROM vendor_data 
WHERE vendor_status='1' 
AND vendor_location='Beijing' 
AND vendor_name='Xiaomi Technology'\G;

-- Invalid: Missing leftmost column
EXPLAIN SELECT * FROM vendor_data 
WHERE vendor_status='1' 
AND vendor_location='Beijing'\G;

-- Partial: Only uses name portion of index
EXPLAIN SELECT * FROM vendor_data 
WHERE vendor_name='Xiaomi Technology'
AND vendor_location='Beijing'\G;

Violating the leftmost prefix rule results in full table scans (ALL access type).

Range Query Impact on Index Usage

When a column in a composite index uses range queries, subsequent columns in the index become ineffective.

EXPLAIN SELECT * FROM vendor_data 
WHERE vendor_name='Xiaomi Technology'
AND vendor_status>'1'
AND vendor_location='Beijing'\G;

The key_len value demonstrates that the location field's index becomes ineffective after the range query on status.

Operations on Indexed Columns

Applying functions or operations to indexed columns causes index invalidation.

EXPLAIN SELECT * FROM vendor_data 
WHERE SUBSTRING(vendor_name, 3, 2)='Technology'\G;

String Type Mismatch

Omitting quotes for string values triggers implicit type conversion, invalidating the index.

EXPLAIN SELECT * FROM vendor_data 
WHERE vendor_status=0 
AND vendor_name='Xiaomi Technology'\G;

Covering Index Strategy

Query only indexed columns to avoid table lookups and leverage covering indexes.

-- Requires table lookup
EXPLAIN SELECT * FROM vendor_data 
WHERE vendor_name='Xiaomi Technology'\G;

-- Uses covering index
EXPLAIN SELECT vendor_name FROM vendor_data 
WHERE vendor_name='Xiaomi Technology'\G;

The Extra field shows using index when covering indexes are utilized.

OR Condition Limitations

Combining indexed and non-indexed columns with OR conditions prevents index usage.

EXPLAIN SELECT * FROM vendor_data 
WHERE vendor_name='Heima Programmer' 
OR created_date='2088-01-01 12:00:00'\G;

Wildcard Placement in LIKE Queries

Leading wildcards in pattern matching disable index usage.

-- Index invalid (leading %)
EXPLAIN SELECT * FROM vendor_data 
WHERE vendor_name LIKE '%Technology'\G;

-- Index valid (trailing %)
EXPLAIN SELECT * FROM vendor_data 
WHERE vendor_name LIKE 'Technology%'\G;

-- Index invalid (both sides %)
EXPLAIN SELECT * FROM vendor_data 
WHERE vendor_name LIKE '%Technology%'\G;

Covering indexes can optimize wildcard queries:

EXPLAIN SELECT vendor_name FROM vendor_data 
WHERE vendor_name LIKE '%Technology%'\G;

Optimizer Decision Making

The query optimizer may choose full table scans when they're more efficient than index usage.

CREATE INDEX idx_vendor_location ON vendor_data(vendor_location);

-- May not use index due to data distribution
EXPLAIN SELECT * FROM vendor_data WHERE vendor_location='Beijing'\G;

-- More likely to use index
EXPLAIN SELECT * FROM vendor_data WHERE vendor_location='Xi\'an'\G;

NULL Value Handling

IS NULL and IS NOT NULL conditions may not use indexes depending on data distribution.

IN vs NOT IN Performance

IN conditions typical use indexes while NOT IN conditions do not.

Composite vs Single-Column Indexes

Prefer composite indexes over multiple single-column indexes.

-- Composite index provides multiple access paths
CREATE INDEX idx_name_status_location ON vendor_data(vendor_name, vendor_status, vendor_location);
-- Equivalent to: name, name+status, name+status+location

-- Single-column indexes limit optimizer choices
CREATE INDEX idx_vendor_name ON vendor_data(vendor_name);
CREATE INDEX idx_vendor_status ON vendor_data(vendor_status);
CREATE INDEX idx_vendor_location ON vendor_data(vendor_location);

Index Usage Monitoring

Monitor index effectiveness through system status variables:

SHOW STATUS LIKE 'Handler_read%';
SHOW GLOBAL STATUS LIKE 'Handler_read%';

Key metrics include:

• Handler_read_key: Number of rows read via index (higher is better)
• Handler_read_rnd_next: Number of rows read via table scans (lower is better)
• Handler_read_first: First index entry reads (indicates full index scans)