I'm dealing with a production level project that has SPATIAL queries for a large table [over 15 million rows]

We are using MariaDB 10.10.6, along with springboot 2.7.18 I've added the extra dependencies for hibernate spatial and the spatial queries work as expected

The project is hosted in a dedicated server and the machine is good enough to work with 96 cores and 256GB ram

The database only has one table with spatial data (a point), this table has about 30 other columns, I've created a spatial index for the specified column and also indexes for most of the other columns

but a simple select to look for rows within a polygon takes about 30 seconds to fill a 20 size page

 select
   *
from
    announces a 
where
    ( 
        ST_Within(a.point, ST_GeomFromText('MULTIPOLYGON(((-46.6043151 -23.5172944, -46.5912849 -23.5172944, -46.5912849 -23.5315056, -46.6043151 -23.5315056, -46.6043151 -23.5172944)))',4326))=1
    ) 
order by
    a.id asc limit 1;

the weirdest thing is if i put coordenates that doesn't exist in the database (such as south pole) it takes only 17 seconds to run

it seems that the index is not even being used:

I thought about moving this table to memory but maria db doesn't allow inmemory engine for tables with spatial

I'm really out of ideas here, can anyone offer me suggestions on how to improve this query/table performance

=================================

Ok i narrowed down the problem, it mariadb is really ignoring the spatial index because of the =1 suffix

the problem is: this suffix is automatically added by hibernate when using the spatial query... the JPA query is:

SELECT a FROM Announce a WHERE  within(a.point, :#{#filter.multiPolygon})=true

however if I remove = true from this query hibernate crashes and the system wont boot up

I wouldn't like to use native query is there any way to tell hibernate to work without making =1????

• the real query is much bigger I'm omitting it for sake of simplicity

I'm dealing with a production level project that has SPATIAL queries for a large table [over 15 million rows]

We are using MariaDB 10.10.6, along with springboot 2.7.18 I've added the extra dependencies for hibernate spatial and the spatial queries work as expected

The project is hosted in a dedicated server and the machine is good enough to work with 96 cores and 256GB ram

The database only has one table with spatial data (a point), this table has about 30 other columns, I've created a spatial index for the specified column and also indexes for most of the other columns

but a simple select to look for rows within a polygon takes about 30 seconds to fill a 20 size page

 select
   *
from
    announces a 
where
    ( 
        ST_Within(a.point, ST_GeomFromText('MULTIPOLYGON(((-46.6043151 -23.5172944, -46.5912849 -23.5172944, -46.5912849 -23.5315056, -46.6043151 -23.5315056, -46.6043151 -23.5172944)))',4326))=1
    ) 
order by
    a.id asc limit 1;

the weirdest thing is if i put coordenates that doesn't exist in the database (such as south pole) it takes only 17 seconds to run

it seems that the index is not even being used:

I thought about moving this table to memory but maria db doesn't allow inmemory engine for tables with spatial

I'm really out of ideas here, can anyone offer me suggestions on how to improve this query/table performance

=================================

Ok i narrowed down the problem, it mariadb is really ignoring the spatial index because of the =1 suffix

the problem is: this suffix is automatically added by hibernate when using the spatial query... the JPA query is:

SELECT a FROM Announce a WHERE  within(a.point, :#{#filter.multiPolygon})=true

however if I remove = true from this query hibernate crashes and the system wont boot up

I wouldn't like to use native query is there any way to tell hibernate to work without making =1????

• the real query is much bigger I'm omitting it for sake of simplicity

• spring-boot
• mariadb
• geospatial
• spatial-query

• Please create a bug report. It sounds like MariaDB was crashing rather than hibernate. The =1 should be optimized away by the server and some recent changes might have already made this possible. The use of spatial index should occur. Some minimal sample data would be of particular use in a bug report. – danblack Commented Nov 18, 2024 at 22:51

1 Answer 1

I found the solution and I hope the answer can help someone in future.
this might be useful to anyone having index problem due to the forced comparision hibernate uses

To clarify I spent long time assuming that I was using spatial wrong, or that the points and indexes were wrong in the database, or that the MultiPolygon was the issue... but none of this was the cause of slow query

The inability of MariaDB of using the spatial index while having an equality for the function result was the problem
At same time hibernate forcing us to use a comparison for the function return would make the query always generate the =1 that destroys the indexed search

I thought of using native = true and that might really had solved the problem but I basically find ugly to have native queries in a JPA project

Instead I extended the dialect used by hibernate to produce a query that looks like what I wanted

public class MariaDB103SpatialDialectExtended extends MariaDB103SpatialDialect {

   public MariaDB103SpatialDialectExtended() {
      super();
      registerFunction("within", new StandardSQLFunction("ST_Within", StandardBasicTypes.BOOLEAN) {
         @Override
         public String render(Type firstArgumentType, List arguments, SessionFactoryImplementor sessionFactory) {
            return super.render(firstArgumentType, arguments, sessionFactory) + " AND 1";
         }
      });
   }
}

This way I overridden the default within function renderization to include a AND 1 so it will be rederized to

ST_Within(a.point, ST_GeomFromText('MULTIPOLYGON(......)',4326)) AND 1=1

MariaDB is smart enough to ignore 1=1 and thus use the spatial index

source: https://stackoverflow/a/61957440