8

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?

Improve this question
0

1 Answer 1

Reset to default
4

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.

Improve this answer
6
  • If a BTREE multi-column index for querying spatial data (e.g. (latitude, longitude) of restaurants) bad? Thank you!
    – user3019105
    Sep 18, 2021 at 15:06
  • 1
    @user3019105 it's not bad but not as efficient as an index specially designed for spatial queries. Also note that the answer is old. You can now have SPATIAL indexes on InnoDB tables.
    – ypercubeᵀᴹ
    Sep 18, 2021 at 15:09
  • Thank you for the quick reply! Why do you say that it is not as efficient as a SPATIAL index? Is it because lat/lng coordinates are real numbers and therefore the chance of having two or more locations with the same latitude is small or even inexistent? In this case if our multi-column index is (latitude, longitude), will MySQL be able to efficiently only find all the locations in a particular range of latitude but then have to scan over all the found records to check for their longitude as well because there are no records or a very small amount of records sharing the same latitude?
    – user3019105
    Sep 18, 2021 at 15:17
  • 1
    Yes, exactly as you describe it. If you have a condition to check if a point in inside an area (say rectangle), something like latitude BETWEEN x1 AND x2 AND longitude BETWEEN y1 AND y2, with a btree 2-column you'll efficiently find the rows that match the latitude part and then scan all the found records for the longitude part.
    – ypercubeᵀᴹ
    Sep 18, 2021 at 15:29
  • 1
    Sorry, removed the link by accident. Here it is again, a related question: How does R-Tree outperform B-Tree for simple check if a point is within a rectangle
    – ypercubeᵀᴹ
    Sep 18, 2021 at 15:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.