1

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.

enter image description here

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

Improve this question

1 Answer 1

Reset to default
2

I'm not so sure you should create distinctive indexes for each column. What I did was to create a FULLTEXT index for 3 columns together, and then search using that. If you want to create different FULLTEXT indexes for each column (it could work I'm not sure) you could do it like that. Create your FULLTEXT indexes by going here, selecting all the columns you want to index as FULLTEXT: enter image description here

Then use something like the following syntax to get your results: 

SELECT * FROM `places` WHERE MATCH (`place_name`) AGAINST ("Cambridge" IN BOOLEAN MODE);
Improve this answer
8
  • Oh so what I essentially did was create FULLTEXT index for 3 seperate columns (postal_code, place_name, admin_name1). What you're saying is to create one FULLTEXT index for 3 columns together. How would I go about creating the index for 3 columns "together"? I'll do some research on the matter
    – jakewies
    Jul 12, 2015 at 18:53
  • When you press new a new pop-up appears where you can select from the dropdown menu to add more than one column. Tell me to add a screenshot if you can't find out how.
    – ChrisG
    Jul 12, 2015 at 18:56
  • Ok working thru it now. Here's my main issue with search. I am searching the database and I want to match MULTIPLE columns with $_GET["geo"]. So I'm running a query like query("SELECT * FROM places WHERE MATCH(postal_code) AGAINST(\"$_GET["geo"]\"). This query returns a JSON array of objects of all the matching rows in the db, BUT ONLY if $_GET["geo"] is a postal code (obviously). I run into errors when I pass MATCH() multiple columns, ie. MATCH(postal_code, place_name, admin_name1) AGAINST(\"$_GET["geo"]\"). This returns an empty JSON array.
    – jakewies
    Jul 12, 2015 at 19:16
  • I've been struggling to understand why this happens but since reading your post above I'm thinking its possibly because I've named a FULLTEXT index for multiple columns, instead of naming the FULLTEXT index for the 3 columns together. On the right track here?
    – jakewies
    Jul 12, 2015 at 19:17
  • I EDITED my OP with my current status
    – jakewies
    Jul 12, 2015 at 19:26

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .