Understanding MySQL Slow Queries, Index Optimization, and Integrating Elasticsearch and HBase

01. Experience of Slow MySQL Queries

Most internet applications are read‑heavy; slow queries often stem from indexing issues, lock contention, and large‑table bottlenecks.

02. Indexes

MySQL uses B+‑tree indexes; proper use of left‑most prefix, index push‑down, and covering indexes can dramatically speed up queries.

03. Causes of Index Failure

WHERE clauses with !=, <>, OR, functions, or expressions on the left side

LIKE patterns that start with %

Unquoted string literals

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

Not matching the leftmost prefix

04. Why These Cause Failure

The B+‑tree cannot efficiently use the index when the above patterns break the ordered nature of the index.

05. Function Operations

Applying functions to indexed columns (e.g., where length(a)=6) prevents index usage because the engine cannot navigate the tree.

06. Implicit Conversions

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

07. Disrupting Order

LIKE '%…' and unquoted strings cause MySQL to skip the index to preserve order.

08. Indexing Low‑Cardinality Columns

Indexes on columns like gender are often ignored when they select more than ~30 % of rows, as a full table scan is cheaper.

09. Simple Index Strategies

Index push‑down and covering indexes

Prefix indexes for long strings

Avoid functions on indexed columns

Consider maintenance cost for write‑heavy tables

10. Choosing the Wrong Index

Mis‑statistics or optimizer mis‑prediction can cause MySQL to pick a low‑selectivity index; use ANALYZE TABLE or FORCE INDEX to correct.

11‑15. Lock and Transaction Issues

MDL locks, FLUSH waits, row locks, and InnoDB's repeatable‑read semantics can block queries.

15‑16. Large‑Table Scenarios

When tables reach billions of rows, index cache misses and I/O become bottlenecks; solutions include sharding (horizontal/vertical), read‑write splitting, and partitioning.

23. Evaluating Elasticsearch

Elasticsearch (ES) is a distributed search engine built on Lucene, suitable for full‑text search, log analysis, and as a NoSQL JSON store.

24. What ES Can Do

Supports real‑time search, Kibana visualisation, and integrates with Logstash (ELK stack).

25‑30. ES Structure and Speed

Uses inverted indexes with a memory‑resident term index (FST) for fast term lookup; excels at tokenised searches but may be slower for exact matches compared to MySQL covering indexes.

31‑35. When to Use ES

Full‑text search where MySQL LIKE is inefficient

Log and analytics workloads

Combine ES for search with MySQL for transactional data (ES + MySQL)

Combine ES with HBase for write‑intensive scenarios

36. HBase Overview

HBase is a column‑family NoSQL store; rows are identified by a RowKey and data is stored column‑wise, making it suitable for write‑heavy workloads.

37‑40. Storage Model, OLTP/OLAP, RowKey Design, Use Cases

RowKey‑based single‑row queries, range scans, or full scans; not ideal for complex ad‑hoc queries but offers high write throughput and scalability.

41. Summary

Effective query performance relies on proper indexing, understanding MySQL internals, and when appropriate, off‑loading search to Elasticsearch or using HBase for massive write workloads.