Slow performance when running repeated complex spatial query in MySQL

Question

I'm searching for circle select by distance. I have one point with latitude & longitude and I want to search if I have in database some points around me. And yes, it's must be a circle!

I'm using this clause in query (I just google it, I can't do math):

((6373 * acos (cos ( radians( 48.568962 ) ) * cos( radians( X(coords) ) ) * cos( radians( Y(coords) ) - radians( 6.821352 ) ) + sin ( radians( 48.568962 ) ) * sin( radians( X(coords) ) )))  <='0.2')

0.2 = 200 meters

I'm using POINT data type
Yes, I have SPATIAL index on it
Yes, I'm trying to use the "spatial" functions, but it's not returning a circle, it's returning some OVAL and i need PRECISE circle

This "circle" clause takes very, very, VERY long time for all tables. When I'm using the OVAL method of SPATIAL foos. It takes maybe 0.1s and that's great! But I need circle and this takes 17 sec, LOL.

Can you help me someone? Thanks a lot guys!

EDIT: spatial functions means some like this:

WHERE ST_Contains(ST_Buffer(
      ST_GeomFromText('POINT(12.3456 34.5678)'), (0.00001*1000)) , coords) <= 1 /* 1 km */

EDIT 2 (table struct.):

I'm expecting 10 rows from this tables of course I have indexes on wz_uuid

select a....., b.... from table_1 a left join table_2 b on a.wz_uuid=b.wz_uuid

And this is not just 2 tables, i have 11 tables *2 like this. (weekly database backups). First tables (_1) have 0-4000 rows, 2-11 have 300k+ rows.

All indexes are relevant and also data types & encoding.

wz_uuid & id - unique, btree index
others - btree indexes
coords - spatial index

Yes, you should use geospatial functions to avoid using the costly Haversine formula. — Tim Biegeleisen
Post your tables and tell us the expected result. the way you are using geospatial functions the index cannot be used. — e4c5
Why I can't use it? I already tested it, but it's oval I just need the correct function I think for circle. I can show you tables but it's just normal optimized table with maybe 30 columns and I need all of them, cause I need to make AVG row. — user4256388
post only the relevent columns. The index cannot be used because youa re buffering. — e4c5
There you go :-) but I think it's not important cause another spatial function can use this index and it's pretty fast. — user4256388

Unknown Unknown · Accepted Answer · 2017-01-04T05:26:05

Great solution from XX sec to 100 ms, that's all I want :-)

Use MySQL spatial extensions to select points inside circle

Slow performance when running repeated complex spatial query in MySQL

1 Answers