How to Specify Join SQL Hint

Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.

Original topic: 请问以下如何指定 Join sql hint

| username: Raymond

Please note: This is a MySQL SQL hint issue.
MySQL version is 8.0.30.
Statement:

select count(*) from customer a, orders b where a.c_phone='25-989-741-2988' and a.c_custkey=b.o_custkey and substr(a.C_COMMENT,1,10)=substr(b.O_COMMENT,1,10);

All data comes from the tpch database, and the statement comes from this post:

Requirement: I don’t know how to add a hint in MySQL to make table a and table b use hash join. According to the official documentation:
https://dev.mysql.com/doc/refman/8.0/en/optimizer-hints.html#optimizer-hints-table-level
It should use BNL or NO_BNL, but I tried several times and still don’t know how to use it. I can only use the following hint to make the optimizer use hash join. Please, teachers, guide me on how to force the optimizer to use hash join in MySQL (just curious, using index join will definitely have better performance).

mysql> explain analyze select /*+ no_index(a PRIMARY) no_index(b indexO_CUSTKEY)*/ count(*) from customer a, orders b where a.c_phone='25-989-741-2988' and a.c_custkey=b.o_custkey and substr(a.C_COMMENT,1,10)=substr(b.O_COMMENT,1,10);

+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Aggregate: count(0)  (cost=2229157716.05 rows=1) (actual time=614.465..614.465 rows=1 loops=1)
    -> Inner hash join (b.O_CUSTKEY = a.C_CUSTKEY), (substr(a.C_COMMENT,1,10) = substr(b.O_COMMENT,1,10))  (cost=2229135302.38 rows=224137) (actual time=614.461..614.461 rows=0 loops=1)
        -> Table scan on b  (cost=16.01 rows=1505768) (actual time=0.060..341.510 rows=1500000 loops=1)
        -> Hash
            -> Filter: (a.C_PHONE = '25-989-741-2988')  (cost=15275.30 rows=14802) (actual time=0.090..47.197 rows=1 loops=1)
                -> Table scan on a  (cost=15275.30 rows=148023) (actual time=0.088..37.339 rows=150000 loops=1)
 |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.62 sec)
| username: Kongdom | Original post link

I don’t understand what it means. Didn’t the original post give an example of MySQL here?

| username: dba远航 | Original post link

Without the table structure, how do we know how to specify it?