Diagnosing Slow Queries and Index Pitfalls in MySQL

When Indexed Columns Fail Due to Functions or Conversions

Consider a table storing trading records:

CREATE TABLE tradelog (
  id int NOT NULL,
  tradeid varchar(32) DEFAULT NULL,
  operator int DEFAULT NULL,
  t_modified datetime DEFAULT NULL,
  PRIMARY KEY (id),
  KEY idx_tradeid (tradeid),
  KEY idx_modified (t_modified)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

A requirement asks for the total records that occurred during July across all available years. A straightforward approach often looks like:

SELECT COUNT(*) FROM tradelog WHERE MONTH(t_modified) = 7;

Even though t_modified is indexed, the execution may be unexpectedly long. The optimizer cannot use the index for efficient tree-based lookups when a function wraps the column — in this case MONTH(). The function distorts the natural ordering of index values, so the optimizer falls back to a full index scan instead of a refined seek.

To let the optimizer exploit the index correctly, rewrite the condition using range comparisons:

SELECT COUNT(*)
FROM tradelog
WHERE (t_modified >= '2016-07-01' AND t_modified < '2016-08-01')
   OR (t_modified >= '2017-07-01' AND t_modified < '2017-08-01')
   OR (t_modified >= '2018-07-01' AND t_modified < '2018-08-01');

Implicit Type Conversions and Character Set Mismatches

Assume we query the same table with an integer value on a varchar column:

SELECT * FROM tradelog WHERE tradeid = 110717;

The tradeid column stores string values, but MySQL compares a number against a string. The internal rule converts the string to a numeric type:

SELECT * FROM tradelog WHERE CAST(tradeid AS signed) = 110717;

Applying CAST() effectively wraps the indexed column inside a function, again preventing tree-assisted searches and leading to a full table scan.

A similar issue arises when joining tables with mismatched character sets. For example, when a UTF-8 column is compared with a utf8mb4 column, MySQL implicitly cnoverts the lesser charset to the greater one:

SELECT * FROM trade_detail
WHERE CONVERT(tradeid USING utf8mb4) = ?;

Again, a function call on the indexed column disables efficient index usage.

Key Takeaways for Preserving Index Usage

Avoid wrapping indexed columns in functions; restructure conditions with direct comparisons or ranges.
Align data types — do not compare strings with integers unless you explicitly handle the conversion on the constant side.
Keep character sets consistent across joined columns to avoid internal conversions.
After schema or query changes, inspect execution plans with EXPLAIN to verify index usage.

Stuck Reads: Locks and Metadata Delays

A simple primary key lookup can hang for two common reasons:

SELECT * FROM t WHERE id = 1;

Issue an SHOW PROCESSLIST command. The state Waiting for table metadata lock indicates another session holds a metadata lock — perhaps an ongoing DDL or an uncommitted transaction that acquired a metadata write lock.

When using locking reads:

SELECT * FROM t WHERE id = 1 LOCK IN SHARE MODE;

If another transaction already holds an exclusive row lock on id = 1 and has not committed, the read blocks until the lock is released.

Slow Reads Even With Small Result Sets

An unindexed column query may need to scan many rows:

SELECT * FROM t WHERE c = 50000 LIMIT 1;

Without an index on c, the engine performs a sequential scan, potentially touching tens of thousands of rows.

A more surprising scenario involves a primary key lookup that reports hundreds of milliseconds in the slow log, while the same row fetched with a locking read completes instantly:

START TRANSACTION WITH CONSISTENT SNAPSHOT;  -- session A
-- session B executes 1,000,000 UPDATEs on id = 1
SELECT * FROM t WHERE id = 1;                 -- consistent read, seems slow
SELECT * FROM t WHERE id = 1 LOCK IN SHARE MODE; -- current read, fast

After session B performs massive updates without committing, it generates extensive undo logs. A consistent read, which follows snapshot semantics, must traverse the entire undo chain to reconstruct the visible row version, whereas a locking read reads the latest committed data directly. The overhead of walking through a million undo entries accounts for the drastic difference in perceived execution time.

Posted on Fri, 07 Aug 2026 16:53:18 +0000 by ry4n0wnz

Freaks City