Mysql range check instead of index usage-CodePudding

I have a table with 35000 records. I have to join it with itself. my problem is that it does not utilize the defined indexes. look at the analyze-query results below: The table:

mysql> desc tbl_organizer_tree;
 ------------------------ ------------------- ------ ----- --------- ---------------- 
| Field                  | Type              | Null | Key | Default | Extra          |
 ------------------------ ------------------- ------ ----- --------- ---------------- 
| node_id             | int               | NO   | PRI | NULL    | auto_increment |
| node_title          | varchar(255)      | YES  | MUL | NULL    |                |
| node_hint           | varchar(1000)     | NO   |     | NULL    |                |
| node_left           | int               | YES  | MUL | NULL    |                |
| node_right          | int               | YES  | MUL | NULL    |                |
 ------------------------ ------------------- ------ ----- --------- ---------------- 
5 rows in set (0.01 sec)

The query:

mysql> EXPLAIN SELECT t1.node_id  FROM tbl_organizer_tree AS t1 INNER JOIN tbl_organizer_tree AS t2 ON (t1.node_left >= t2.node_left AND
t1.node_left <= t2.node_right)\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
   partitions: NULL
         type: index
possible_keys: node_left
          key: node_left
      key_len: 5
          ref: NULL
         rows: 33931
     filtered: 100.00
        Extra: Using index
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: t2
   partitions: NULL
         type: ALL
possible_keys: node_left,node_right
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 33931
     filtered: 11.11
        Extra: Range checked for each record (index map: 0x30)
2 rows in set, 1 warning (0.00 sec)

the query-analyze:

mysql> EXPLAIN ANALYZE SELECT t1.node_id  FROM tbl_organizer_tree AS t1 INNER JOIN tbl_organizer_tree AS t2 ON (t1.node_left >= t2.node_l
eft AND t1.node_left <= t2.node_right)\G

*************************** 1. row ***************************
EXPLAIN: -> Nested loop inner join  (cost=115134799.90 rows=127898053) (actual time=0.648..298146.405 rows=32491076 loops=1)
    -> Covering index scan on t1 using node_left  (cost=3449.35 rows=33931) (actual time=0.181..69.022 rows=33901 loops=1)
    -> Filter: ((t1.node_left >= t2.node_left) and (t1.node_left <= t2.node_right))  (cost=0.01 rows=3769) (actual time=0.087..8.741 rows=958 loops=33901)
        -> Index range scan on t2 (re-planned for each iteration)  (cost=0.01 rows=33931) (actual time=0.086..7.326 rows=20455 loops=33901)

1 row in set (5 min 0.32 sec)

Even though both node_left,node_right are defined as a table index, they are ignored during query execution and it took 5 minutes long to be finished.

I'm looking for any suggestion that improves performance here. Even MySQL configuration and settings are welcome.

Edits:

MySQL version is 8.0.27

CodePudding user response：

You can use an index hint which forces MySQL (or MariaDB) to use any of the mentioned indexes:

SELECT t1.node_id  
FROM tbl_organizer_tree AS t1 
INNER JOIN tbl_organizer_tree AS t2 USE INDEX(node_left,node_right)
  ON (t1.node_left >= t2.node_left AND t1.node_left <= t2.node_right)

Please let me know if that helps to improve the speed of your query.

CodePudding user response：

Assuming you currently have

INDEX(node_left),
INDEX(node_right)

I suggest you replace them with

INDEX(node_left, node_right),
INDEX(node_right, node_left)

That may help some of the time.

I do not recommend index hints -- they tend to hurt performance for some situations.