Hi everyone.
I'd like to know if any of you has experienced drawbacks/problems with the FULLTEXT type of index. But let me give you some background first:
I'm working on a Content Management System. The texts stored in mySQL should be searchable. Now I don't claim to fully understand how MySQL uses Indexes but I understand that (sub-)indexes on VARCHAR or TEXT fields have no use on queries like "... WHERE content LIKE '%$searchstring%'"
This is completely reasonable to me.
On one hand that means that when the column 'content' stores only a single word (like in a keywordlist) I can use a subindex in combination with "WHERE content LIKE '$searchstring%'". That's fine for me.
On the other hand it means that on a large number of texts in the database a true fulltext search will become too slow since a subindex would be useless and mySQL would scan the whole dataset.
Now I've read about the FULLTEXT type of index that allowes me to do a case-insensitive wordsearch on columns. Fine.
The first disadvantage I discovered is that only true words will be found but not subwords. Thus if a text contains "automobile" and I search for "auto" it will not be found. Can this be solved?
Are there other problems with FULLTEXT indexes?
As always thx in advance,
Dominique
I'd like to know if any of you has experienced drawbacks/problems with the FULLTEXT type of index. But let me give you some background first:
I'm working on a Content Management System. The texts stored in mySQL should be searchable. Now I don't claim to fully understand how MySQL uses Indexes but I understand that (sub-)indexes on VARCHAR or TEXT fields have no use on queries like "... WHERE content LIKE '%$searchstring%'"
This is completely reasonable to me.
On one hand that means that when the column 'content' stores only a single word (like in a keywordlist) I can use a subindex in combination with "WHERE content LIKE '$searchstring%'". That's fine for me.
On the other hand it means that on a large number of texts in the database a true fulltext search will become too slow since a subindex would be useless and mySQL would scan the whole dataset.
Now I've read about the FULLTEXT type of index that allowes me to do a case-insensitive wordsearch on columns. Fine.
The first disadvantage I discovered is that only true words will be found but not subwords. Thus if a text contains "automobile" and I search for "auto" it will not be found. Can this be solved?
Are there other problems with FULLTEXT indexes?
As always thx in advance,
Dominique