Less restrictive query return less result due to simple removing one additional constraint

Look at this query

SELECT DISTINCT
  TB.ID,
  TB.Latitude,
  TB.Longitude,
  111151.29341326*SQRT(pow(-6.185-TB.Latitude,2)+pow(106.773-TB.Longitude,2)*0.98839228980165) AS Distance
FROM
  `tablebusiness` AS TB
   join tableauxiliary as TA on TA.BusinessID=TB.ID
WHERE
MBRContains(
   GeomFromText ('MULTIPOINT(-6.2317830813328 106.72621691867,-6.1382169186672  106.81978308133)'),
   TA.Latlong
   )
   AND
MATCH (FullTextSearch) AGAINST ('kucing*' IN BOOLEAN MODE)
ORDER BY
  Distance
LIMIT
  0, 20

that's basically search all bizs whose TA.LatLong is in the box of 'MULTIPOINT(-6.2317830813328 106.72621691867,-6.1382169186672 106.81978308133)' and after that the box must contain kucing

This returns 22 rows.

Now compared that to this query

SELECT DISTINCT
  TB.ID,
  TB.Latitude,
  TB.Longitude,
  111151.29341326*SQRT(pow(-6.185-TB.Latitude,2)+pow(106.773-TB.Longitude,2)*0.98839228980165) AS Distance
FROM
  `tablebusiness` AS TB
   join tableauxiliary as TA on TA.BusinessID=TB.ID
WHERE
MBRContains(
   GeomFromText ('MULTIPOINT(-6.2317830813328 106.72621691867,-6.1382169186672  106.81978308133)'),
   TA.Latlong
   )
ORDER BY
  Distance
LIMIT
  0, 20

It's the same query but LESS restrictive. Now I only want to check whether the business is in the box or not. That is it. Yes there are businesses in those box. Most of which do not contain the word "kucing*"

There should be more queries. Yet there are less. The second query return null. The LESS restrictive query return LESS result.

Rolando answer is good with one catch.

The one with
MATCH (FullTextSearch) AGAINST ('kucing*' IN BOOLEAN MODE) is the one that works properly.

I am asking why the one without is problematic. Rolando seems to try to correct the correct ones.

Note: (I wonder whether this should be another question). After listening to others I tried:
After removing distinct

```
SELECT
TB.ID,
111151.29341326SQRT(pow(-6.185-TB.Latitude,2)+pow(106.773-TB.Longitude,2)0.98839228980

The FULLTEXT index acts very funny with regard to the MySQL Query Optimizer. I have written about this before:

• https://stackoverflow.com/a/6092216/491757 (May 23, 2011)

• FULLTEXT index ignored in BOOLEAN MODE with 'number of words' conditional (Oct 25, 2011)

• Mysql fulltext search my.cnf optimization (Jan 26, 2012)

• MySQL EXPLAIN doesn't show 'use index' for FULLTEXT (May 07, 2012)

Try refactoring your first query so that the FULLTEXT clause and the Spatial clause are not performed in the same level of the EXPLAiN plan.

SELECT * FROM
(
    SELECT DISTINCT
        TB.ID,
        TB.Latitude,
        TB.Longitude,
        111151.29341326*SQRT(pow(-6.185-TB.Latitude,2) + 
        pow(106.773 - TB.Longitude,2)*0.98839228980165) AS Distance
    FROM
    (
        SELECT * FROM `tablebusiness`
        WHERE MATCH (FullTextSearch) AGAINST
        ('kucing*' IN BOOLEAN MODE)
    ) AS TB
    join 
    (
        SELECT ID FROM tableauxiliary
        WHERE MBRContains(GeomFromText ('MULTIPOINT
        (-6.2317830813328 106.72621691867,-6.1382169186672 106.81978308133)'),TA.Latlong)
    ) as TA
    on TA.BusinessID=TB.ID
    ORDER BY
        Distance
) AA LIMIT 0, 20;

StackExchange Database Administrators Q#19661, answer score: 4