Why Your MySQL Queries Are Slow and How to Fix Them with Indexes, ElasticSearch, and HBase

1. What Does a Slow MySQL Query Feel Like?

Most internet applications are read‑heavy; a slow query hurts performance. Common reasons include index problems, MDL locks, flush waits, row locks, and large‑table bottlenecks.

1.1 Index

When data volume is modest, most slow queries can be solved with proper indexes; many are caused by bad indexes.

MySQL indexes are B+ trees; interviewers love to ask about left‑most prefix, B+ trees, and related concepts.

Composite indexes follow the left‑most prefix rule; they improve speed because of index‑pushdown and covering indexes.

1.1.1 Why Do Indexes Fail?

Indexes may be unused; use EXPLAIN to diagnose. Common causes:

WHERE uses !=, <>, OR, functions, or expressions.

LIKE with a leading %.

String literals not quoted.

Low‑cardinality columns (e.g., gender).

Missing left‑most prefix.

1.1.2 Why These Causes Break Indexes

MySQL avoids using an index if it thinks the operation would break the index order.

Function Operations

Using functions on indexed columns (e.g., WHERE LENGTH(a)=6) prevents index use because the value is transformed before lookup.

Implicit Conversion

Implicit type or character‑set conversion can also invalidate indexes, especially in join queries.

Disrupting Order

LIKE '%…' or unquoted strings may be treated as order‑breaking, so MySQL drops the index.

1.1.3 Why Not Index Low‑Cardinality Fields?

Low‑cardinality fields like gender give little benefit; scanning may be cheaper than using the index.

InnoDB may skip such indexes when they cover more than ~30 % of rows.

1.1.4 Simple Index Practices

Index push‑down: use composite indexes for multi‑condition queries.

Covering index: keep all needed columns in the index to avoid table look‑ups.

Prefix index: index only the first N characters of a string.

Avoid functions on indexed columns.

Consider maintenance cost for write‑heavy tables.

1.1.5 When MySQL Chooses the Wrong Index

Too many indexes can cause the optimizer to pick a low‑cardinality one, leading to excessive scans. Fix by running ANALYZE TABLE or using FORCE INDEX.

1.2 MDL Locks

MySQL 5.5 introduced metadata locks (MDL). CRUD acquires a read MDL; DDL acquires a write MDL. Write MDL blocks read MDL; check with SHOW PROCESSLIST for “Waiting for table metadata lock”.

1.3 Flush Waits

Flush commands can be blocked; monitor with SHOW PROCESSLIST for “Waiting for table flush”.

1.4 Row Locks

Uncommitted write locks cause blocking.

1.5 Current Read (Repeatable Read)

InnoDB’s default repeatable‑read may need to walk undo logs to present a consistent snapshot.

1.6 Large‑Table Scenarios

Tables with billions of rows stress I/O and CPU; InnoDB buffer pool may miss, leading to cache eviction. Typical solutions: sharding (horizontal/vertical) and read‑write separation.

1.6.1 Sharding

Choose sharding strategy based on bottleneck: I/O → vertical sharding; CPU → horizontal sharding. Tools: Sharding‑Sphere, TDDL, Mycat.

1.6.2 Read‑Write Separation

When reads far exceed writes, use master‑slave replication to split load and improve availability.

2. How to Evaluate ElasticSearch

ElasticSearch (ES) is a near‑real‑time distributed search engine built on Lucene, used for full‑text search, JSON document storage, log analysis, etc.

2.1 What Can It Do?

Supports full‑text search, NoSQL JSON storage, monitoring logs; often paired with Logstash and Kibana (ELK).

2.2 ES Architecture

Before 7.0: index → type → document (like DB → table → id). After 7.0, “type” is removed; treat index as a table.

Key components: mapping (field definitions) and settings (shard/replica count).

Example mapping shows “text” fields with a “keyword” sub‑field.

2.3 Why Is ES Fast?

ES uses inverted indexes: term → posting list. It adds an in‑memory term index (FST) to quickly locate terms in the term dictionary, reducing disk seeks.

2.3.1 Tokenized Search

Tokens are indexed, enabling fast prefix matches (e.g., “Ada”).

2.3.2 Exact Search

Exact term lookup may be comparable to MySQL covering indexes.

2.4 When to Use ES

2.4.1 Full‑Text Search

Ideal for fuzzy keyword queries, e.g., chat message search.

2.4.2 Composite Queries

Combine ES for search‑heavy fields with MySQL for transactional data, or ES + HBase for massive storage.

2.5 Summary

ES is fast because of its inverted index and term‑index layer; use it for text‑heavy workloads.

3. HBase Overview

HBase stores data by column families, not rows. RowKey is the primary key, sorted lexicographically; columns are dynamic.

It is suited for write‑intensive workloads, not real‑time low‑latency queries. Typical use cases involve massive data ingestion where reads are limited to RowKey scans.

4. Conclusion

Software development should be incremental; choose the right tool for the problem. Diagnose bugs first, then optimize.