2 Similar Queries Run at Vastly Different Speed in a Very Surprising Way

Question

I have to be very careful here. Both queries are so similar that the only different is the area searched.

What's surprising is the query that run on BIGGER AREA, and naturally yield MORE ROWS, is the one that run way FASTER.

In fact the only reason why I want to run the second query is to narrow down the search because the original query returns too many results.

SELECT BusinessID
FROM tableauxiliary
WHERE MBRWithin( Latlong, GeomFromText( 'MULTIPOINT(-6.2209869856406 106.73701301436,-6.1490130143594 106.80898698564)' ) )
AND MATCH (
FullTextSearch
)
AGAINST (
'res*'
IN BOOLEAN
MODE
)

returns

Showing rows 0 - 29 ( 5,007 total, Query took 0.0226 sec)

Now my code will streamline things up.

I asked the same query except for SMALLER regions

SELECT BusinessID
FROM
  tableauxiliary
WHERE
  MBRContains(
        GeomFromText (
            'MULTIPOINT(-6.1928749092968 106.7651250907,-6.1771250907032 106.7808749093)'
            ),
            Latlong)    
  AND MATCH (FullTextSearch) AGAINST ('res*' IN BOOLEAN MODE)
AND Prominent >15 

Showing rows 0 - 29 ( 30 total, Query took 15.3889 sec)

What gives?

Answer 1

Hard to tell with out seeing the data and an explanation of the query but one uses contains and the other within - may be those are implemented differently on your system (knowing that might also help answers).

Answer 2

Probably your fulltext index is not used in the second query. An error of the optimization algorithm choosing the wrong path. BTW: you should add which database you are using (MySQL?), this is important information. Eg MySQL spatial support is still limited.