Take the 2-minute tour ×
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.

I want to find businesses near a point that contain a word that start with sea

lat=-6.185068&long=106.773121&keyword=sea&distance=5000&startfrom=0&howmany=20&prominent=5

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
  TB.Latitude >-6.2317830813328 AND TB.Latitude < -6.1382169186672
   AND TB.Longitude >106.72621691867 AND TB.Longitude <106.81978308133
  AND MATCH (TA.FullTextSearch) AGAINST ('sea*' IN BOOLEAN MODE)
AND TA.Prominent >5
ORDER BY
  Distance
LIMIT
  0, 20

I got these result:

rasane__-6.18_106.77    -6.1826789706189    106.774375525435    299.434343596808
seafood-bangka---jakarta__-6.18_106.77  -6.18410749805068   106.767679452896    596.253784623613
seafood-sari-laut__-6.19_106.78     -6.18661497692636   106.7816047167  967.65389600759
rasane-seafood-restaurant__-6.18_106.76     -6.18244550827744   106.763621456218    1074.56073316741
pecel-lele-lela__-6.19_106.78   -6.18836286783996   106.782370851856    1100.91660371997
bintang-laut-seafood__-6.18_106.77  -6.17782494896301   106.766016483307    1109.75958732106
d'cost-itc-permata-hijau__-6.19_106.77  -6.19494204046746   106.770504692048    1138.95344788546
masterpiece---car-leather-seat-and-interior__-6.18...   -6.18373417378944   106.783719062805    1192.82955595065
seafood-makasar__-6.18_106.78   -6.17684    106.780666  1241.07275264282
hongkong-jumbo-seafood-restaurant__-6.20_106.77     -6.19668602280699   106.771334150698    1311.89593536338
plg-(pecel-lele-greenville)__-6.17_106.78   -6.17354759635866   106.7768121112  1340.84148631128
makassar__-6.18_106.78  -6.17990504916375   106.784087464957    1349.76048368113
makassar-seafood__-6.20_106.77  -6.19642790400783   106.768355369568    1370.00078243887
pelangi-seafood__-6.17_106.78   -6.1733876537374    106.777818202972    1396.23099579268
sea-food-sari-laut-kemanggisan__-6.19_106.79    -6.188395   106.7853    1410.61411729396
seafood-toni-k__-6.19_106.79    -6.188435   106.785291  1410.85254243932
seafood-sari-laut-tony-koeswoyo__-6.19_106.79   -6.188445   106.785417  1424.55989808634
makassar-seafood-restaurant__-6.18_106.78   -6.177669   106.783744  1439.9871117091
sea-food-38-kebon-jeruk__-6.20_106.77   -6.19710303086272   106.767730987066    1465.8643124913
seafood-38-(lap.-bola)__-6.20_106.77    -6.197078   106.767655  1466.67274960557

Now I run the similar query based on something slightly different lat=-6.185564&long=106.775518&keyword=Sea&distance=5000&startfrom=0&howmany=20&prominent=5 Notice that the lat and long differs only a little bit

The query doesn't change much

SELECT DISTINCT
  TB.ID,
  TB.Latitude,
  TB.Longitude,
  111151.29341326*SQRT(pow(-6.186-TB.Latitude,2)+pow(106.776-TB.Longitude,2)*0.98838855059148) AS Distance
FROM
  `tablebusiness` AS TB
  join tableauxiliary as TA on TA.BusinessID=TB.ID
WHERE
  TB.Latitude >-6.2327830813328 AND TB.Latitude < -6.1392169186672
   AND TB.Longitude >106.72921691867 AND TB.Longitude <106.82278308133
  AND MATCH (TA.FullTextSearch) AGAINST ('Sea*' IN BOOLEAN MODE)
AND TA.Prominent >5
ORDER BY
  Distance
LIMIT
  0, 20

This one returns empty

I put the 2 queries side by side. Obviously many of the result are within the rectangle.

How come?

Can anyone spot some obvious bugs in these? Maybe I did something wrong in SQL command

share|improve this question
1  
This bug report (which turned out not to report a bug, but a (for me at least) cryptic feature) can be useful for you. You write sea* here and Sea* there. I found the link by searching for case sensitivity in full text search. –  dezso Jun 22 '12 at 9:36
    
Please turn that into an answer. That is PRECISELY the problem. –  Jim Thio Jun 22 '12 at 9:42
    
However TA.FullTextSearch is only one column and the collation is UTF8. So I wonder why it's still case sensitive? –  Jim Thio Jun 22 '12 at 10:05
    
another problem is the collation. It's utf8bin rather than utfcollation –  Jim Thio Jun 22 '12 at 10:52

1 Answer 1

up vote 2 down vote accepted

You write sea* here and Sea* there. This bug report (which turned out not to report a bug, but a (for me at least) cryptic feature) can be useful for you. (I found the link by searching for case sensitivity in full text search.)

share|improve this answer

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.