I have a table CREATE TABLE passengers ( pid int(11) NOT NULL, pname varchar(50) DEFAULT…
- User
I have a table CREATE TABLE passengers ( pid int(11) NOT NULL, pname varchar(50) DEFAULT 'somebody', sex bit(1) DEFAULT NULL, age decimal(5,2) NOT NULL, PRIMARY KEY (pid) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; ALTER TABLE sailors.passengers ADD COLUMN e_mail VARCHAR(45) NULL AFTER age;
And i had an index CREATE INDEX age_sex ON passengers(age, sex);
I performed EXPLAIN SELECT * FROM passengers WHERE age = 0 AND sex = 0;
And then I dropped previous index (age, sex) and created a new one, (sex, age). ALTER TABLE sailors.passengers DROP INDEX age_sex ; CREATE INDEX sex_age ON passengers(sex, age);
In EXPLAIN SELECT with the same select, I see that type and rows havent changed, but key_len decreased from 5 to 2. How does this value being calculated and what does it mean in context of optimization?
EXPLAIN SELECT * FROM passengers WHERE sex = 1 ORDER BY age;
- Assistant
In MySQL, the
EXPLAINstatement provides valuable insights into how the query optimizer intends to execute a query. One of the key pieces of information it provides is thekey_lenvalue, which indicates the length of the key (in bytes) that MySQL decides to use from the index for executing the query. Understanding howkey_lenis calculated and its implications on query optimization can help you design more efficient indexes.Understanding
key_len-
Definition:
key_len: Represents the number of bytes from the index that MySQL uses to execute the query. It essentially indicates how much of the index is utilized for filtering rows.
-
Calculation:
- For single-column indexes,
key_lencorresponds to the size (in bytes) of that column. - For multi-column indexes,
key_lenis the sum of the sizes of the indexed columns from left to right until MySQL can no longer use the index for efficient lookups based on the query conditions.
- For single-column indexes,
-
Example with Your Table:
- Table Definition:
CREATE TABLE passengers ( pid int(11) NOT NULL, pname varchar(50) DEFAULT 'somebody', sex bit(1) DEFAULT NULL, age decimal(5,2) NOT NULL, e_mail VARCHAR(45) NULL, PRIMARY KEY (pid) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; - Indexes:
- Original Index:
(age, sex) - Modified Index:
(sex, age)
- Original Index:
- Table Definition:
Analyzing Your Scenario
-
Original Index
(age, sex):- Column Sizes:
age:DECIMAL(5,2)typically uses 3 bytes (though storage can vary based on MySQL version and settings).sex:BIT(1)uses 1 byte (since MySQL aligns bits to byte boundaries).
key_lenCalculation:age(3 bytes) +sex(1 byte) = 4 bytes.- MySQL might include some overhead, leading to the reported
key_lenof 5 bytes.
- Query Execution:
EXPLAIN SELECT * FROM passengers WHERE age = 0 AND sex = 0;- MySQL can utilize both
ageandsexcolumns in the index for filtering, resulting in a largerkey_lenas both columns contribute to narrowing down the results.
- MySQL can utilize both
- Column Sizes:
-
Modified Index
(sex, age):key_lenCalculation:sex(1 byte) +age(3 bytes) = 4 bytes.- However, the reported
key_lenis 2 bytes.
- Explanation:
- When the index order is changed to
(sex, age), and the query is stillWHERE age = 0 AND sex = 0, MySQL prioritizes the first column in the index (sex) for filtering. - Partial Index Usage: MySQL uses
sexto filter rows, resulting in akey_lenthat reflects only the portion of the index used primarily (sex), which is 2 bytes in this context. - The second condition (
age = 0) is then applied as a post-filter, which might not leverage the index as efficiently becauseageis no longer the leading column.
- When the index order is changed to
Implications on Optimization
-
Larger
key_len:- Indicates that more of the index is being utilized.
- Typically results in more selective filtering directly via the index, leading to fewer rows scanned and faster query execution.
-
Smaller
key_len:- Suggests that only a portion of the index is being used.
- May lead to less efficient filtering, requiring more rows to be scanned and potentially increasing query execution time.
Applying to the Second Query
EXPLAIN SELECT * FROM passengers WHERE sex = 1 ORDER BY age;-
With Index
(sex, age):- Optimal Usage: The index
(sex, age)perfectly supports this query.- Filtering:
sex = 1directly uses the first column of the index. - Ordering:
ORDER BY ageis also supported becauseageis the second column in the index.
- Filtering:
key_len: Likely 4 bytes (1 byte forsex+ 3 bytes forage).- Benefits:
- Efficient filtering and ordering without needing additional sorting.
- Minimizes the number of disk I/O operations.
- Optimal Usage: The index
-
With Index
(age, sex):- Less Optimal:
- Filtering:
sex = 1is the second column, so MySQL might not use the index efficiently for filtering bysex. - Ordering:
ORDER BY agealigns with the first column, but sincesexis second, sorting might still benefit.
- Filtering:
key_len: Likely smaller (onlyageis used initially).- Drawbacks:
- Potential need for additional row scanning or sorting, reducing performance.
- Less Optimal:
Best Practices for Indexing
-
Order Matters:
- Place columns that are frequently used in WHERE clauses first in the index.
- Consider the most selective columns first to maximize filtering efficiency.
-
Covering Indexes:
- If possible, design indexes that cover all columns used in the query to avoid accessing the table data separately.
-
Composite Indexes:
- Use composite indexes for queries that filter or sort on multiple columns, ensuring the index order aligns with query patterns.
Conclusion
The
key_lenvalue in theEXPLAINoutput is crucial for understanding how effectively MySQL utilizes your indexes. In your case:- Original Index
(age, sex): Fully utilized both columns for the query, resulting in a higherkey_lenand more efficient filtering. - Modified Index
(sex, age): Partially utilized the index for the same query, leading to a lowerkey_lenand potentially less efficient filtering.
When designing indexes, always consider the specific queries your application runs most frequently and arrange index columns to maximize their utility based on those patterns.
-
Other models, same or similar prompt
Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.