Understanding Slow Queries, Index Optimization, and Search Solutions with MySQL, Elasticsearch, and HBase

1. MySQL Query Slow Experience

Most internet applications are read‑heavy and write‑light, so fast reads are essential; many slow queries stem from index problems.

1.1 Index

MySQL uses B+ trees; proper composite indexes enable index‑pushdown and covering indexes, dramatically reducing table lookups.

1.1.1 Causes of Index Failure

Common reasons include using !=, <> , OR, functions or expressions on indexed columns, leading‑wildcard LIKE, missing quotes, low‑cardinality columns, and not matching the leftmost prefix.

1.1.2 Why These Cause Failure

Functions and implicit conversions prevent the optimizer from using the B+ tree efficiently.

Function Operations

Example: where length(a) = 6 forces a full scan because the function breaks the index order.

Implicit Conversion

Implicit type or character‑set conversion can also invalidate index usage.

1.1.3 Low‑Cardinality Fields

Indexes on low‑cardinality fields (e.g., gender) often degrade performance because the optimizer may skip them.

1.1.4 Simple Index Practices

Index pushdown: use composite indexes for multi‑condition queries.

Covering index: keep all needed columns in the index to avoid table lookups.

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

Avoid functions on indexed columns.

Consider maintenance cost for frequently updated tables.

1.1.5 Evaluating Wrong Index Choice

Sometimes MySQL picks a low‑selectivity index; you can run ANALYZE TABLE x to refresh statistics or use FORCE INDEX to guide the optimizer.

1.2 MDL Locks

MySQL 5.5 introduced metadata locks (MDL); read locks are taken for CRUD, write locks for DDL. Use SHOW PROCESSLIST to see statements waiting for Waiting for table metadata lock.

1.3 Flush Wait

Flush commands can block queries; monitor with SHOW PROCESSLIST for Waiting for table flush.

1.4 Row Locks

Uncommitted write locks cause other transactions to wait.

1.5 Current Read

InnoDB default isolation is REPEATABLE READ; current read may need to traverse undo logs.

1.6 Large Table Scenarios

For tables with billions of rows, even with good indexing, aggregation can hit I/O or CPU bottlenecks.

1.6.1 Sharding (Database/Table Splitting)

Choose vertical sharding (different databases/tables) for I/O bottlenecks, horizontal sharding for CPU bottlenecks. Tools: Sharding‑Sphere, TDDL, Mycat.

1.6.2 Read‑Write Splitting

When reads far exceed writes, use master‑slave replication to distribute read traffic and improve availability.

1.7 Summary

Common MySQL slow‑query causes and mitigation methods are listed, along with large‑scale strategies like sharding and read‑write splitting.

2. How to Evaluate Elasticsearch

Elasticsearch (ES) is a real‑time distributed search engine based on Lucene, suitable for full‑text search, log analysis, and NoSQL JSON storage.

2.1 What It Can Do

Use cases include full‑text search, monitoring logs, and data analytics, often together with Logstash and Kibana (ELK stack).

2.2 ES Structure

Before ES 7.0: Index → Type → Document; after 7.0, Type is removed, leaving Index as the logical table.

2.3 Why ES Queries Are Fast

ES uses inverted indexes with a term dictionary and an in‑memory term index (FST), enabling rapid term lookup compared to MySQL’s disk‑based indexes.

2.3.1 Tokenized Search

Tokenized terms allow fast matching, e.g., searching for “Ada” directly locates matching documents.

2.3.2 Exact Search

For exact matches, ES may be comparable to MySQL’s covering indexes.

2.4 When to Use ES

Full‑text search where MySQL’s LIKE is inefficient.

Combining ES with MySQL: store searchable fields in ES (with IDs) and full records in MySQL.

Combining ES with HBase for massive write‑intensive workloads.

2.5 Summary

ES’s speed comes from its inverted index and in‑memory term index; it excels at tokenized searches but is not a universal replacement for relational queries.

3. HBase Overview

HBase stores data by column families rather than rows, making it suitable for write‑heavy workloads.

3.1 Storage Model

Rows are identified by a RowKey (lexicographically sorted); each column family can have dynamic columns (cells).

3.2 OLTP vs. OLAP

HBase is optimized for OLTP‑style writes, not for complex analytical queries (OLAP).

3.3 RowKey Design

Only three query patterns are supported: single‑row lookup, range scan, and full table scan; good RowKey design is critical.

3.4 Use Cases

Ideal for write‑intensive scenarios where fast ingestion is needed and queries are limited to known RowKeys or small ranges.

4. Overall Summary

Software development should be incremental; the right technology fits the problem better than the newest one. To speed up queries, first identify and fix bugs, then apply appropriate indexing, sharding, or search‑engine solutions.