1. index merge
1) hint usage
- select /*+ INDEX_MERGE(payment) */
- select /*+ NO_INDEX_MERGE(payment) */
2. multi-range read (MRR)
1) hint usage
- select /*+ MRR(city) */
2) related buffer parameter
when this option use, you can increase "read_rnd_buffer_size" for improving random read.
3. Batched Key Access
1) hint usage
- select /*+ BKA(ci) */
2) related buffer parameter
when this option use, you can increase "join_buffer_size" for improving performance.
4. Derived Merge
1) hint usage
- select /*+ MERGE(~) */
- select /*+ NO_MERGE(~) */
5. Engine Condition Pushdown (Only NDB Cluster)
1) hint usage
- None
6. Index Condition Pushdown
1) hint usage
- select /*+ NO_ICP(~) */
7. Index Extensions (MySQL will consider the primary key columns as part of the index)
1) hint usage
- None
8. Index Visibility
1) hint usage
- None
9. Loose Index Scan
1) hint usage (disable)
- select /*+ NO_RANGE_OPTIMIZATION() */
10. Range Access Method (involve the operators like >, >=, =<, between, in(), is null, like etc)
1) hint usage (disable)
- select /*+ NO_RANGE_OPTIMIZATION() */
2) related buffer parameter
when this option use, you can increase "range_optimizer_max_mem_size" for performance.
11. Semijoin (after MySQL 8.0.16, in the case of antijoin like not exists, after MySQL 8.0.17)
1) hint usage
- Duplicate weedout (like removing duplicates using temp table)
a. select /*+ SEMIJOIN(DUPSWEEDOUT) */
b. select /*+ NO_SEMIJOIN(DUPSWEEDOUT) */
- firstmatch
a. select /*+ SEMIJOIN(FIRSTMATCH) */
b. select /*+ NO_SEMIJOIN(FIRSTMATCH) */
- loosescan
a. select /*+ SEMIJOIN(LOOSESCAN) */
b. select /*+ NO_SEMIJOIN(LOOSESCAN) */
12. Skip Scan (after MySQL 8.0.13)
1) hint usage
- select /*+ SKIP_SCAN() */
- select /*+ NO_SKIP_SCAN() */
13. Subquery Materialization (stores the result of a subquery in an internal temp table)
1) hint usage
- select /*+ SUBQUERY(MATERIALIZATION) */
'RDB > MySQL' 카테고리의 다른 글
| MySQL 8.0 Hint for set value parameter (0) | 2023.06.22 |
|---|---|
| MySQL 8.0 Hint for Query block (0) | 2023.06.22 |
| MySQL OOM 최소화 (0) | 2023.04.27 |
| delay replication (0) | 2023.03.28 |
| semisynchronous replication (0) | 2023.03.28 |