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

MySQL queries often become slow when the workload is read‑heavy and indexes are missing or misused; the article starts by describing typical index‑related causes such as missing left‑most prefix, function operations, implicit type conversion, low cardinality fields, and not matching the leftmost prefix.

Common reasons for index failure are listed, including using where length(a) = 6, like '%pattern', unquoted strings, low‑selectivity columns, and not using the leftmost prefix.

To evaluate why an index is ineffective, the author suggests checking the execution plan with EXPLAIN, re‑analyzing tables using analyze table x, or forcing a specific index with force index.

Additional MySQL performance issues such as MDL locks, flush waits, and row‑level locks are discussed, with commands like show processlist to identify waiting sessions.

For large tables, the article recommends sharding strategies (horizontal vs. vertical), read‑write separation, and tools such as Sharding‑Sphere, TDDL, or Mycat.

Elasticsearch is introduced as a fast, distributed search engine built on Lucene; its basic operations (cluster health, shard status, mapping, settings) are shown with commands like GET /_cat/health?v&pretty, GET /_cat/shards?v, GET yourindex/_mapping, and GET yourindex/_settings. A sample search request is provided:

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

The article explains why Elasticsearch queries are fast due to inverted indexes, term dictionaries, and an in‑memory term index (FST), while also noting that not all queries are faster than MySQL.

Typical use cases for Elasticsearch include full‑text search, log analysis, and scenarios where MySQL keyword searches are inefficient.

HBase is then described as a column‑oriented NoSQL store; its storage model (row key, timestamp, column families, cells) and the importance of row‑key design are highlighted. The distinction between OLTP (row‑oriented) and OLAP (column‑oriented) workloads is clarified, noting that HBase excels at write‑intensive workloads but offers limited query capabilities.

Finally, the article summarizes that solving slow queries starts with proper index design, understanding database internals, and, when necessary, combining technologies such as MySQL, Elasticsearch, and HBase to achieve scalability and performance.