B
B
Boris2018-02-27 01:58:41
MySQL
Boris, 2018-02-27 01:58:41

MySQL, spatial index and Doctrine - how to make them work together?

I have a MySQL table with a field of type POINT and Spatial key on it using ORM Doctrine.
I'm looking for a point to hit the area using MBRContains ().
Wrote a dql adapter for MBRContains.
Doctrine requires that in the WHERE block after the function there should be one of the signs [=, <, <=, <>, >, >=, !=], and this is generally logical. Therefore, I added MBRContains (...) = 1 in the WHERE query. Everything works.
BUT!
I started testing and saw that the index was not being used. Explain doesn't even show it in possible_keys.
I run different query options in the console and see that "WHERE MBRContains(...)" uses an index, but "WHERE MBRContains(...) = 1" does not. But Doctrine does not allow writing "WHERE MBRContains(...)" in DQL.
How can you make friends with them? Or force MySQL to use an index?
FORCE INDEX doesn't help.
At the very least, the option remains to use raw sql, and then map the results, but I would like to do without it

Answer the question

In order to leave comments, you need to log in

1 answer(s)
B
Boris, 2018-02-27
@zIs

Found a crooked solution through a dirty and disgusting hack.
In the dql adapter for MBRContains, I added "AND 1" at the end of the function definition in getSql(), and in the query I write "MBRContains(...) AND 1=1".
Now the index is used, everything works, and I will pray that no one sees this shame :)

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question