Say I want 20 closest businesses to a location.
Innodb doesn’t support that. So what does?
Is it possible to have some tables in some engine and other tables on different engines?
Yes you can use MyISAM for storing the geo location as they support the spatial data types.For more information please look Saptial Datatypes in MySQL
And You can also use the mix of engines there is no issues on that..
Here is how to design an optimal schema, plus code, to find the 20 closest whatevers: