Can spatial index be a part of a multiple columns index?

Question

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?

Answer 1

No, you can't.

An index, in MySQL, will either be

• a BTREE single or multi-column index.

• a HASH index (available only for MEMORY tables), that can span several columns.

• a SPATIAL index (available only for MyISAM tables).
  (update: They are available for InnoDB tables as well, since version 5.7.

• a FULLTEXT index (available for MyISAM and 5.6.4+ InnoDB tables), that can span several columns of CHAR, VARCHAR or TEXT type.

You can't combine these types.