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 MATCH()