This manual over here is extremely unclear and doesn't even provide some sample SQL statements: http://dev.mysql.com/doc/refman/5.6/en/column-indexes.html
Another way to rephrase the question is the following:
We know we can have an index with multiple columns. What about if the indexes of those columns are of different type? Say the first column is spatial, the other is fulltextsearch, etc. Can we do so in mysql? (Bonus: can we do so in mongodb, if you happen to know)
Say you have a myisam table
It has a LATLONG column that contains points
It has a FULLTEXT column that contains words in the "business"
You want to query by LATLONG first, and then within the matching LATLONG you want to filter based on FULLTEXT column.
I suppose you will need multiple column index.
But what is the SQL command?
As we know, mysql will always use fulltextsearch index first if possible.
This query:
SELECT BusinessID as ID ,
111151.29341326*SQRT(pow(-6.186751-X(LatLong),2)+pow(106.772835-Y(LatLong),2)*0.98838574205337) AS Distance from tableauxiliary
use index (LatLong_2)
WHERE
MBRContains(
GeomFromText (
'MULTIPOINT(-6.1934985598076 106.76604791159,-6.1800034401924 106.77962208841)'
),
Latlong)=1
AND Prominent >15
AND MATCH FullTextSearch AGAINST ('sea*' IN BOOLEAN MODE)
ORDER BY
Distance
LIMIT
0, 45
Takes a long time, while this query:
SELECT BusinessID as ID ,
111151.29341326*SQRT(pow(-6.186751-X(LatLong),2)+pow(106.772835-Y(LatLong),2)*0.98838574205337) AS Distance from tableauxiliary
use index (LatLong_2)
WHERE
MBRContains(
GeomFromText (
'MULTIPOINT(-6.1934985598076 106.76604791159,-6.1800034401924 106.77962208841)'
),
Latlong)=1
AND Prominent >15
AND MATCH FullTextSearch AGAINST ('sea*' IN BOOLEAN MODE)
ORDER BY
Distance
LIMIT
0, 45
is faster because I tell mysql to use latlong_2 index instead, which is a spatial query.
Well, say I want to have a multiple column index. Latlong_2 and FULLTEXTSEARCH. They are off different type. LatLong_2 is spatial and FULLTEXTSEARCH is a fulltext search index. What SQL command I should run?
