Can;t get MySql/Mariadb to use spatial index

Question

This is using 5.5.68-MariaDB. Yes, I know this is very old but I have to use this version for this application for the time being.

The explain and the performance of the query clearly show the indexes aren't being used. Here's the info:

explain select bdcfabric.location_id from bdccoverage, bdcfabric where
 st_intersects(bdccoverage.shape,bdcfabric.bxlocation)\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: bdccoverage
         type: ALL
possible_keys: SHAPE
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 886
        Extra:
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: bdcfabric
         type: ALL
possible_keys: bxlocation_idx
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1105588
        Extra: Using where; Using join buffer (flat, BNL join)

The indexes:

show indexes from bdccoverage\G
*************************** 4. row ***************************
        Table: bdccoverage
   Non_unique: 1
     Key_name: SHAPE
 Seq_in_index: 1
  Column_name: SHAPE
    Collation: A
  Cardinality: NULL
     Sub_part: 32
       Packed: NULL
         Null:
   Index_type: SPATIAL
      Comment:
Index_comment:

show indexes from bdcfabric\G
*************************** 2. row ***************************
        Table: bdcfabric
   Non_unique: 1
     Key_name: bxlocation_idx
 Seq_in_index: 1
  Column_name: bxlocation
    Collation: A
  Cardinality: NULL
     Sub_part: 32
       Packed: NULL
         Null:
   Index_type: SPATIAL
      Comment:
Index_comment:

The schema:

+------------+-------------+------+-----+---------+----------------+
| Field      | Type        | Null | Key | Default | Extra          |
+------------+-------------+------+-----+---------+----------------+
| SHAPE      | geometry    | NO   | MUL |         |                |

describe bdcfabric;
+-------------------------+------------+------+-----+---------+-------+
| Field                   | Type       | Null | Key | Default | Extra |
+-------------------------+------------+------+-----+---------+-------+
| bxlocation              | point      | NO   | MUL |         |       |
+-------------------------+------------+------+-----+---------+-------+

Why aren't the indexes being used?

Answer 1

Your query needs to run all locations of bdcfabric.bxlocation against bdccoverage.shape in the other table. These kinds of joins where the join criteria is complex (like st_intersects) don't lead themselves to index usage.

Even on this old version, increasing join_buffer_size / join_buffer_space_limit. Look at the session rows_read on the default size for the query compared to when you increase it.