4 Add the mysql tag since this question is specifically for that.
| link

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

3 Add the mysql tag since this question is specifically for that.
| link
    Tweeted twitter.com/#!/StackDBAs/status/325338063054184449
2 added 1846 characters in body
source | link

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?

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

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?

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?

1
source | link