Jump to content

Adding Fulltext Index


chibineku

Recommended Posts

I am trying to implement a search function on a database table using query expansion, and I'm aware that I have to add the FULLTEXT index on the fields I want to become searchable. I have tried to do this and got no errors from PHPMyAdmin, but when I examine the structure of the table the cardinality of the FULLTEXT index is 0. I have tried inidividually setting the FULLTEXT index and it seems to work, but my serach returns the error: Can't find FULLTEXT index matching the column listAny ideas what's going wrong?

Link to comment
Share on other sites

Hm...I dropped the faulty indices and did each one one at a time, then repaired the table and it looks like the changes all took, but I still get the same error when I try to make the query.I can make the query successfully against the title column of my table, which is VARCHAR, but not to the category column, also VARCHAR, nor any others that are VARCHAR or TEXT. I tried them one at a time. I have also retried setting the FULLTEXT index onto all seven columns at once, but that didn't work either.

Link to comment
Share on other sites

Tried that, and tried creating another table with the FULLTEXT index as part of the create query - nada.

Link to comment
Share on other sites

Absolutely sure. Interesting: it works if I query each column separately, but not several of them comma separated.

Link to comment
Share on other sites

SELECT * FROM aromaProducts WHERE MATCH (title, keywords) AGAINST ('patchouli');with and without the addition of WITH QUERY EXPANSION

Link to comment
Share on other sites

With a query like that you need a single index that covers both fields. This will work if you have an index on each field:SELECT * FROM aromaProducts WHERE MATCH (title) AGAINST ('patchouli') OR MATCH (keywords) AGAINST ('patchouli');

Link to comment
Share on other sites

I have another table, aromaProducts1, which I indexed during creation and PHPMyAdmin shows that there is one index, called title, covering 7 other fields with the FULLTEXT attribute. I don't know what the appropriate terms are for that, but instead of listing each keyname, then the attribute FULLTEXT then the column name, it just has one word as the keyname. And the same search from that table, with the same data in it, also produces the same error.

Link to comment
Share on other sites

FYI, someone over at stackoverflow found the answer: once you've created an index than covers a few fields, you need to search within ALL of those fields. If you want another combination of fields, you need to create another index covering that subset. Madness it is, but works it does :)

Link to comment
Share on other sites

Doesn't that strike you as a bit strange? I mean, either let me use the index name as a shortcut for searching all those columns (can i do that?) or let me assign each as fulltext and search whatever ones I want. Seems a bit fussy.

Link to comment
Share on other sites

You can assign an index to each and search however many you want, you just need to search them individually instead of together. That's the difference between this:MATCH (title, keywords) AGAINST ('patchouli')and this:MATCH (title) AGAINST ('patchouli') OR MATCH (keywords) AGAINST ('patchouli')When you try to search in both fields at once, you're specifically telling MySQL that you're trying to search in a single index, that's what that syntax means. The contents of each MATCH is a single index, if it doesn't find an index covering whatever is in MATCH it's an error. You can do it either way, they just have different syntax.

Link to comment
Share on other sites

And is there no shorthand way of referencing a specific index? Say I call an index 'full' and it covers 7 columns...is there syntax to allow me to just use the word 'full' instead of writing all seven column names? I know that I only really have to do it a few times when making PHP scripts that will automate the process, but it'd be good to know if that were possible. Btw I have consulted the manual and there isn't a great deal of detail about this, which is why I'm asking you and not trying to do my own research.

Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...