Best practices for data query
AnalyticDB for MySQL distributes data across nodes. SQL performance depends heavily on how well your queries align with this distribution. This topic covers the core rules for writing and optimizing SQL statements.
Quick reference
Use this table to identify which optimization applies to your situation:
| Scenario | Optimization | Section |
|---|---|---|
Query uses SELECT * |
Select only needed columns | Select only the columns you need |
| Multiple filter conditions, some low-selectivity | Use internal scan for low-selectivity conditions | Combine index scans with internal scans |
Slow query due to substr(), cast(), or type mismatch |
Rewrite to use raw column with range condition | Avoid conditions that invalidate indexes |
Filter includes IS NOT NULL alongside a range condition |
Remove the redundant IS NOT NULL |
Remove redundant IS NOT NULL conditions |
| Multi-table join scans too much data | Add filter conditions to both sides of the join | Optimize multi-table joins |
| High QPS with millisecond latency requirements | Include partition key in WHERE clause | SQL development rules |
SQL development rules
Write simple SQL statements
Single-table queries on denormalized data consistently outperform multi-table joins because they avoid cross-node coordination overhead. In a distributed system, joins require the engine to serialize data, move it between nodes, and then reassemble results — each step adds latency. Where possible, flatten related data into a single table.
Reduce I/O operations
Fetch only the columns your query actually needs. Because AnalyticDB for MySQL uses hybrid row-column storage, each additional column in the result set adds I/O and memory overhead. Fetching fewer columns reduces both.
Use distributed computing
Design queries to keep computation local to each node. Queries that require data to move between nodes — such as unbounded cross-node joins — force the engine to serialize and transmit data, which limits parallelism and increases latency.
Use partition pruning
For workloads that require high queries per second (QPS) and millisecond response times, include partition key conditions in your SQL statements. This lets the engine skip irrelevant partitions entirely, reading only the data that can match your query.
SQL optimization rules
Select only the columns you need
Specify column names explicitly instead of using SELECT *. Because column count directly affects I/O, selecting unnecessary columns wastes both I/O and memory. This is especially significant in hybrid row-column storage, where each column is read independently.
Avoid:
SELECT * FROM tab1 WHERE c1 > 100 AND c1 < 1000;Use:
SELECT col1, col2 FROM table_name WHERE c1 > 100 AND c1 < 1000;Combine index scans with internal scans
When a query has multiple filter conditions, use the index on the selective condition (the one that returns fewer rows) and use an internal scan for less-selective conditions. This avoids the overhead of index lookups on columns that match a large fraction of the table.
AnalyticDB for MySQL supports the no_index_columns hint to force internal scans on specific columns. The engine retrieves rows using the indexed column, then reads and filters the specified column row by row using internal record pointers.
The no_index_columns hint applies to engine versions earlier than 3.14. For version 3.14 and later, use the filter_not_pushdown_columns hint instead. For details, see Filter conditions without pushdown.
Use internal scan for range conditions
In the following query, c1 = 3 is highly selective (matches approximately 10,000 rows), but time >= '2010-01-01 00:00:00' matches a large portion of the table. Using an index on time returns too many intermediate rows, making it less efficient than a scan.
-- Inefficient: index on both columns
SELECT c1, c2 FROM tab1 WHERE c1 = 3 AND time >= '2010-01-01 00:00:00';Apply the hint to keep the c1 index and switch time to internal scan:
/*+ no_index_columns=[tab1.time] */
SELECT c1, c2 FROM tab1
WHERE c1 = 3 AND time >= '2010-01-01 00:00:00';The engine uses the c1 index to get the matching row set, then reads the time value for each row and applies the time filter in place. This avoids loading the full time index for a range that matches most of the table.
Use internal scan for inequality conditions
The <> operator cannot efficiently filter rows using an index — the index narrows rows to a small set, but <> excludes only one value and still matches most of the table. Force an internal scan on the inequality column:
/*+ no_index_columns=[tab1.c2] */
SELECT c1, c2 FROM tab1 WHERE c1 = 3 AND c2 <> 100;Use internal scan for LIKE with leading wildcards
LIKE patterns with a leading wildcard — LIKE '%abc' or LIKE '%abc%' — cannot use an index because there is no fixed prefix to scan. Force an internal scan to avoid a full index traversal:
/*+ no_index_columns=[tab1.c3] */
SELECT c1, c2 FROM tab1 WHERE c1 = 3 AND c3 LIKE '%abc%';Avoid conditions that invalidate indexes
When the engine cannot use an index for a filter condition, it falls back to a full table scan. For large tables, this significantly degrades query performance. The following conditions cause index failures:
| Condition type | Example | Why it fails | Fix |
|---|---|---|---|
| Function on a column | substr(cast(time AS varchar), 1, 10) |
Wrapping a column in a function prevents the engine from matching index values | Rewrite as a range condition on the raw column |
| Type mismatch | Comparing a TIMESTAMP column to a string literal |
Implicit conversion prevents index matching | Match the literal type to the column type |
| LIKE with leading wildcard | LIKE '%abc%' |
No fixed prefix to scan | Use no_index_columns hint for internal scan, or restructure the query |
Example: rewrite a function condition to restore index use
The time column is of TIMESTAMP type. Applying substr(cast(...)) forces a full scan:
-- Avoid: function conversion invalidates the index
SELECT c1, c2 FROM tab1
WHERE substr(cast(time AS varchar), 1, 10) = '2017-12-10';Rewrite as a range condition on the raw column to let the engine use the index:
SELECT c1, c2 FROM tab1
WHERE time >= '2017-12-10 00:00:00' AND time <= '2017-12-10 23:59:59';Remove redundant IS NOT NULL conditions
A range condition such as c1 > 100 already excludes NULL values — NULL cannot satisfy a comparison operator, so the result set cannot contain NULL rows. Adding IS NOT NULL alongside it is redundant and adds no filtering benefit.
Avoid:
SELECT c1, c2 FROM tab1 WHERE c1 > 100 AND c1 < 1000 AND c1 IS NOT NULL;Use:
SELECT c1, c2 FROM tab1 WHERE c1 > 100 AND c1 < 1000;Optimize multi-table joins
In a distributed system, filters applied before the join step reduce the data that needs to move between nodes. The optimization rules depend on the table types involved:
| Join type | Optimization rule |
|---|---|
| Fact table joined with fact table | Include a join condition on the partition column, or add WHERE clause filters to reduce the data each side must transmit |
| Replicated table joined with fact table | No special constraints apply; replicated tables are available on all nodes |
Apply filter conditions to all tables involved in the join, not just the driving table. This allows the engine to filter each table independently before the join, reducing the intermediate data set.
Before optimization — only t2 is filtered before the join:
SELECT count(*)
FROM t1 C JOIN t2 O ON C.t1_id = O.t1_id
WHERE O.t2_time BETWEEN '2018-07-20 10:00:11' AND '2018-09-30 10:00:11'
AND O.t2_amount = 100;After optimization — both t1 and t2 are filtered before the join, so the engine can skip irrelevant partitions on both sides:
SELECT count(*)
FROM t1 JOIN t2 ON t1.id = t2.id
WHERE t1.time BETWEEN '2017-12-10 00:00:00' AND '2017-12-10 23:59:59'
AND t1.type = 100
AND t2.time BETWEEN '2017-12-10 00:00:00' AND '2017-12-10 23:59:59'
AND t2.type = 100;When both tables share the same time and type filter, duplicate the condition across both tables. The engine filters each table independently before the join, reducing the intermediate data set transferred between nodes.