Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

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?

share|improve this question
    
What database are you using? –  RyanDalton Jun 29 '12 at 15:47
    
It looks to me like it is MySQL –  CHenderson Jul 30 '12 at 11:27
add comment

2 Answers

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).

share|improve this answer
    
Sorry they both use the same thing at first but I tweaked that. No that is not the cause. –  Jim Thio Jul 31 '12 at 3:25
add comment

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.

share|improve this answer
    
I used mysql. The problem is the fact that the spatial part need to be executed first. However, the optimization algorithm execute the less restrictive full text search first. –  Jim Thio Jul 31 '12 at 3:26
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.