# Common Optimizer Hints and Examples

```
SELECT /*+ USE_INDEX(t1, idx1) */ count(*) FROM t t1 WHERE t1.a = 100;

SELECT /*+ INL_JOIN(t1, t2) */ * FROM t1, t2 WHERE t1.id = t2.id;

SELECT /*+ HASH_JOIN(t1, t2) */ * FROM t1, t2 WHERE t1.id = t2.id;

SELECT /*+ MERGE_JOIN(t1, t2) */ * FROM t1, t2 WHERE t1.id = t2.id;

SELECT /*+ HASH_AGG() */ count(*) FROM t1, t2 WHERE t1.a > 10 GROUP BY t1.id;

SELECT /*+ STREAM_AGG() */ count(*) FROM t1, t2 WHERE t1.a > 10 GROUP BY t1.id;

SELECT /*+ MEMORY_QUOTA(1024 MB) */ * FROM t;
```
----------------------------------------------------
Here are some more examples of `Optimizer Hints`:

- `SELECT /*+ USE_INDEX(t1, idx1) */ count(*) FROM t t1 WHERE t1.a = 100;` directs the use of the index `idx1` on table t1.
- `SELECT /*+ INL_JOIN(t1, t2) */ * FROM t1, t2 WHERE t1.id = t2.id;` specifies the use of `IndexJoin` as the table join method.
- `SELECT /*+ HASH_JOIN(t1, t2) */ * FROM t1, t2 WHERE t1.id = t2.id;` specifies the use of `HashJoin` as the table join method.
- `SELECT /*+ MERGE_JOIN(t1, t2) */ * FROM t1, t2 WHERE t1.id = t2.id;` specifies the use of `MergeJoin` as the table join method.
- `SELECT /*+ HASH_AGG() */ count(*) FROM t1, t2 WHERE t1.a > 10 GROUP BY t1.id;` specifies the use of `HASH_AGG` as the aggregation method.
- `SELECT /*+ STREAM_AGG() */ count(*) FROM t1, t2 WHERE t1.a > 10 GROUP BY t1.id;` specifies the use of `STREAM_AGG` as the aggregation method.
- `SELECT /*+ MEMORY_QUOTA(1024 MB) */ * FROM t;` restricts the memory usage of the TiDB server for this SQL query to 1024 MB.

For more detailed information and additional `Optimizer Hints`, please refer to the official TiDB documentation: https://docs.pingcap.com/tidb/stable/optimizer-hints#optimizer-hints