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.

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.

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 ThioAug 3 '12 at 10:50

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 NoachAug 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 ThioAug 7 '12 at 5:23

SUGGESTION #1

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.

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 ThioAug 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 ThioAug 21 '12 at 6:48

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.

Subquery takes 1 second. It should be a few milisecond.
–
Jim ThioAug 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 RiffelAug 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 ThioAug 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