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?
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.