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