Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

This is my current 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
(
    SELECT *
    FROM
      tableauxiliary
    WHERE
      MBRContains(
    GeomFromText (
        'MULTIPOINT(-6.1934985598076 106.76604791159,-6.1800034401924 106.77962208841)'
        ),
        Latlong)=1  
    AND Prominent >15 
) AS TA

    Having Distance <= 18238
    ORDER BY
  Distance
LIMIT
  0, 45

Notice that they I used subquery. The reason why it uses subquery is because I want

      MBRContains(
    GeomFromText (
        'MULTIPOINT(-6.1934985598076 106.76604791159,-6.1800034401924 106.77962208841)'
        ),
        Latlong)=1   

to be done first. This reduces query time from 19 seconds to .9 seconds.

Is there a way to hint to mysql query optimizer so that I do not need to use subquery

Update:

I tried:

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,FullTextSearch)
WHERE
    MBRContains(
    GeomFromText (
        'MULTIPOINT(-6.1934985598076 106.76604791159,-6.1800034401924 106.77962208841)'
        ),
        Latlong)  
    AND Prominent >15 
    AND MATCH FullTextSearch AGAINST ('a*' IN BOOLEAN MODE)
    ORDER BY
  Distance

Nothing change. If I use explain FullTextSearch is the only index used.

Note: The question is indeed about speed. What happen is I am sure that the query will run faster if mysql optimizer do spatial first before the fulltextsearch. This is because sometimes we are searching for a* in fulltext search that obviously don't limit by much.

Basically I have businesses database for the whole world and I am only interested in businesses within 500 meter square or 10km square at most. So obviously the spatial part will be far more restrictive.

While I am moving to mongodb, it'll be kind of nice if things work too for mysql.

share|improve this question
    
If someone would tell me that mysql simply can't do this, it'll be fine. I am moving out of mysql anyway. The spatial sucks big time. I don't know how facebook deals with mysql and do spatial. –  Jim Thio Aug 3 '12 at 10:50
    
Can anyone tell me why no body can answer this? Is this beyond mysql? –  Jim Thio Aug 5 '12 at 6:55
    
Speaking from a SQL Server perspective, the subquery normally IS the only way to (try) to force order of operations. When you are filtering on a Function, most RDBMS will launch many parallel threads to do everything at once since function calls are inherently slow. My guess is that MySQL is building a list of rows that match your filter, and getting all your rows back from the SELECT at the same time, then filtering down post-retrieval. –  JNK Aug 6 '12 at 15:39
1  
To answer the query, there are two questions I need to have answered: how many rows result from the subquery (a single row or more? guessing more); Also, what is the problem you have with using a subquery? That is, why is it that you want to change the query (is it hogging memory? Other reason?) –  Shlomi Noach Aug 7 '12 at 5:03
    
I would like to thank jcolebrand for offering bounty. I am moving to mongodb anyway because that's what my other start up users see. It's kind of nice though to get this work. I love myql and it sadden me how it can't do this simple things. –  Jim Thio Aug 7 '12 at 5:23
show 4 more comments

2 Answers 2

I have rather bad news for you in this regard

MySQL Query Optimizer tends to stray away for further optimization once it sees a FULLTEXT index. I have written about this before in the StackExchange

You will have refactor the query

My suggestion would be to do the one of the following

SUGGESTION #1

  • Retrieve Business IDs suing the FULLTEXT Search
  • Perform GeoSpatial Search on the Retrieved IDs

SUGGESTION #2

  • Retrieve Business IDs using GeoSpatial Search (Since you said it take 0.9 seconds)
  • Perform the FULLTEXT Search on the GeoSpatial-Retrieved Businesses IDs

SUGGESTION #3

  • Retrieve Business IDs using GeoSpatial Search (Since you said it take 0.9 seconds) in Subquery1
  • Retrieve Business IDs using FULLTEXT Search in Subquery2
  • INNER JOIN Subquery1 and Subquery2

SUGGESTION #4

  • Retrieve Business IDs using GeoSpatial Search (Since you said it take 0.9 seconds)
  • DO NOT USE FullTextSerach. Use REGEXP against GeoSpatial-Retrieved Businesses IDs

Here is your original 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,FullTextSearch)
WHERE
    MBRContains(
    GeomFromText (
        'MULTIPOINT(-6.1934985598076 106.76604791159,-6.1800034401924 106.77962208841)'
        ),
        Latlong)  
    AND Prominent >15 
    AND MATCH FullTextSearch AGAINST ('a*' IN BOOLEAN MODE)
    ORDER BY
  Distance;

If the MySQL Query Optimizer detects a FULLTEXT search along with the conjunction AND followed by an expression within the same WHERE clause (thus, the same level of evaluation within an EXPLAIN plan), the MySQL Query Optimizer will take a vacation after working with the FULLTEXT search.

Perhaps you could try something like this (from SUGGESTION #1):

SELECT BusinessID as ID,  
    111151.29341326*SQRT(pow(-6.186751-X(LatLong),2)+
    pow(106.772835-Y(LatLong),2)*0.98838574205337) AS Distance
FROM
(
    SELECT BusinessID,LatLong
    FROM
    (
        SELECT BusinessID,Prominent,LatLong
        From tableauxiliary WHERE
        MATCH FullTextSearch AGAINST ('a*' IN BOOLEAN MODE)
    ) AA
    WHERE Prominent > 15
) A
WHERE
    MBRContains(
    GeomFromText (
        'MULTIPOINT(-6.1934985598076 106.76604791159,-6.1800034401924 106.77962208841)'
        ),
        Latlong)  
ORDER BY
    Distance
;

Notice you do not see the conjunction AND. Please check the EXPLAIN plan for this. The query should do the following:

  • Gather BusinessID,Prominent,LatLong That Matched the FULLTEXT search
  • Remove BusinessID,LatLong Whose Prominent value is < 15
  • Check Distance of remaining BusinessID values
  • sort the Check Distance results by Distance

UPDATE 2012-08-08 11:30 EDT

As stated earlier, the FULLTEXT index seems to abandon all hope of using other indexes. Perhaps two subqueries that are JOINed (SUGGESTION #3)

SELECT BusinessID as ID,  
    111151.29341326*SQRT(pow(-6.186751-X(LatLong),2)+
    pow(106.772835-Y(LatLong),2)*0.98838574205337) AS Distance
FROM
(
    SELECT BusinessID,Prominent From tableauxiliary WHERE
    MATCH FullTextSearch AGAINST ('a*' IN BOOLEAN MODE)
) A
INNER JOIN
(
    SELECT BusinessID From tableauxiliary WHERE
    MBRContains(
    GeomFromText (
        'MULTIPOINT(-6.1934985598076 106.76604791159,-6.1800034401924 106.77962208841)'
        ),
        Latlong)
) B
USING (BusinessID)
WHERE Prominent > 15
ORDER BY
    Distance
;

You may want to abandon using FULLTEXT searching altogether and try regular expressions (SUGGESTION #4)

SELECT B.BusinessID as ID,  
    111151.29341326*SQRT(pow(-6.186751-X(B.LatLong),2)+
    pow(106.772835-Y(B.LatLong),2)*0.98838574205337) AS Distance
FROM
(
    SELECT BusinessID From tableauxiliary WHERE
    MBRContains(
    GeomFromText (
        'MULTIPOINT(-6.1934985598076 106.76604791159,-6.1800034401924 106.77962208841)'
        ),
        Latlong)
) A INNER JOIN tableauxiliary B USING (BusinessID)
WHERE B.Prominent > 15 AND
((B.FullTextSearch REGEXP ' a*') OR (B.FullTextSearch REGEXP '^a*'))
ORDER BY
    Distance
;
share|improve this answer
    
This is nice. WHat I want to do is to do the MBRContains first before the FUlltext search. The MBR contains take a mere .2-.3 seconds and left a mere 600 rows to analyze further. The problem is optimizer think full text search should be done first. –  Jim Thio Aug 8 '12 at 7:18
    
What you do is effectively using subquery, something that I already do. I am just wondering if ther eis a better way. –  Jim Thio Aug 21 '12 at 6:48
add comment

For SQL Server we have the option of the FORCE ORDER hint. The only comparable I'm aware of for MySQL is STRAIGHT_JOIN.

STRAIGHT_JOIN is similar to JOIN, except that the left table is always read before the right table. This can be used for those (few) cases for which the join optimizer puts the tables in the wrong order.

However, I'm inclined to suggest that if you're getting the desired result by using a sub-query, stick with it.

share|improve this answer
    
Subquery takes 1 second. It should be a few milisecond. –  Jim Thio Aug 7 '12 at 5:22
2  
@Jim Thio - You have a good question (+1), but your question isn't about improving the speed. You are welcome to ask that as another question. –  Leigh Riffel Aug 7 '12 at 17:10
    
I upvoted you. The question is indeed about speed. What happen is I am sure that the query will run faster if mysql optimizer do spatial first before the fulltextsearch. This is because sometimes we are searching for a* in fulltext search that obviously don't limit by much. –  Jim Thio Aug 8 '12 at 7:19
    
Just in case anyone is curious, the bounty was awarded for giving traction/views to the question. –  jcolebrand Aug 13 '12 at 14:13
    
I didn't select answers yet. Let everyone else does. What exactly do you mean jcole? –  Jim Thio Aug 21 '12 at 6:49
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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