Is there a way to alter the minimum character count using FULLTEXT indices in the
places table for the mashup database? Adding a FULLTEXT index to the
admin_code1 column, which holds abbreviations for all of the states (AK, CA, FL, etc.) still won't return any rows if I use a search query such as
SELECT * FROM places WHERE MATCH(admin_code1) AGAINST("CA"). I'm guessing this is a result of
admin_code1 being only 2 letters.
EDIT: I've TRUNCATED places and started fresh, adding a new index containing the three columns like you've stated. Here is the structure of the index called search.
After adding the index I ran a simple query to see if I could return a row:
SELECT * FROM places WHERE MATCH(postal_code) AGAINST("02138")
but I get this response (in that scary red color we all hate):
#1191: Can't find FULLTEXT index matching the column list
So even after adding that new index (have I added it incorrectly?) I don't get any response from the DB.
EDIT #2: After the first try I ran a new query:
SELECT * FROM places WHERE MATCH(postal_code, place_name, admin_name1) AGAINST("02138")
This query returned the row containing the postal code of 02138. The 3 columns passed to
MATCH() are the 3 columns I added to the search index, so that makes sense. What doesn't make sense is that when running the query in my first EDIT, passing only postal_code, I get an error. I guess I'm just curious as to why I got that error when passing only 1 column. Shouldn't the query still be successful?
EDIT 3: Ran a third query testing
IN BOOLEAN MODE:
SELECT * FROM places WHERE MATCH(postal_code) AGAINST("02138" IN BOOLEAN MODE)
and this returned the row expected. Doing some doc reading on
IN BOOLEAN MODE now. Still don't quite understand why using
IN BOOLEAN MODE would return the desired row if only 1 column is passed to
MATCH() and not using
IN BOOLEAN MODE yells at me when I only pass 1 column to