Understanding MySQL Slow Queries, Index Optimization, and Integration with Elasticsearch and HBase

MySQL slow queries often stem from improper index usage; most applications are read‑heavy, so missing or ineffective indexes dramatically increase latency. The article reviews B+‑tree indexes, left‑most prefix rules, index push‑down, covering indexes, and enumerates typical index‑failure causes such as using WHERE length(a) = 6, LIKE '%pattern', implicit type conversion, low‑cardinality columns, and not matching the leftmost prefix.

When an index is built but the query remains slow, the EXPLAIN plan can reveal failures. Common remedies include avoiding functions on indexed columns, ensuring proper prefix usage, and recognizing that low‑selectivity fields like gender should not be indexed because the optimizer may skip them when they occupy more than ~30% of rows.

Beyond indexing, the article discusses other MySQL performance blockers: MDL locks (metadata locks) that block reads/writes, flush waits, row‑level locks, and the impact of InnoDB’s MVCC current‑read mechanism. For very large tables, sharding (horizontal or vertical) and read‑write splitting are recommended to alleviate I/O or CPU bottlenecks.

Elasticsearch is introduced as a distributed, near‑real‑time search engine built on Lucene. It excels at full‑text search, log analytics, and JSON document storage. The article shows a Kibana query and its equivalent Dev Tools request:

GET yourIndex/_search {
  "from": 0,
  "size": 10,
  "query": { "match_phrase": { "log": "xxx" } }
}

It explains the inverted index structure (term index, term dictionary, posting list) and why ES can be faster for tokenized searches, while exact matches may still favor MySQL covering indexes.

Practical ES usage tips include checking cluster health ( GET /_cat/health?v&pretty), shard status, mapping ( GET yourindex/_mapping), and settings ( GET yourindex/_settings). The article also demonstrates analyzing text with:

POST yourindex/_analyze {
  "field": "yourfield",
  "text": "我可真是个机灵鬼"
}

and discusses when to prefer ES (full‑text, log search) versus MySQL (structured, exact queries).

HBase is presented as a column‑family NoSQL store optimized for write‑intensive workloads. It stores data by column families, uses a lexicographically ordered rowkey, and supports only three query patterns: single‑row get, range scan, and full table scan. The article contrasts row‑oriented MySQL with column‑oriented HBase, outlines OLTP vs. OLAP distinctions, and notes that HBase is not suited for complex ad‑hoc queries.

Finally, the article summarizes that effective query performance requires proper index design, awareness of MySQL locking mechanisms, and, when data volume or query patterns exceed relational limits, integrating Elasticsearch for search and HBase for massive write workloads, while always considering the trade‑offs of each technology.