7 replaced http://stackoverflow.com/ with https://stackoverflow.com/
source | link
6 replaced http://dba.stackexchange.com/ with https://dba.stackexchange.com/
source | link
5 added 1974 characters in body
source | link

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

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
;

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

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
;
4 added 171 characters in body
source | link
3 added 1683 characters in body; added 266 characters in body
source | link
2 added 50 characters in body
source | link
1
source | link