How do I get spatial index feature if I mainly use innodb?

Question

My current strategy is to create another table in myisam that contains those spacial information. Then if I want to find 20 closest business, I'll just use join.

Is this a good idea?

Answer 1

Using MyISAM is probably your best and only bet to be able to use a spatial index. You might consider storing the data in an InnoDB table and then export data continuously or by batch, according to your needs, to a myisam table. Then use the myisam table for your queries.

I wouldn't trust myisam as storage engine if data is of importance.

Seems like you figured out the solution yourself.

Answer 2

The way I did it is I add another table that is in myisam containing latitutude and longitude. Then set the ID of the original table as the ID of the auxiliary table. When I look up I do join.

SELECT DISTINCT
  TB.ID,
  TB.Latitude,
  TB.Longitude
FROM
  TableBusiness AS TB
  TableAuxiliary as TA on TA.BusinessID=TB.ID
WHERE
  //bla bla bla do some spatial thingy here
LIMIT
  0, 20

However, myisam spatial index is very dissapointing.

I can't even do nearest neighbor search. At least there is no way to do so with just one mysql command. I got to try a small rectangle and then make the rectangle bigger and bigger.

If there is, please tell me what it is.