Performance of BLOB under InnoDB, search

[Anon]

Since InnoDB can't full text index BLOB or TEXT, is it a bad idea to use it for the main content table in my system (assuming that I will want a search engine)? Does anyone have experience with the performance of text queries on InnoDB tables?

The alternative would be to run a higher-level indexer or spider in a different "thread", feeding into a separate keyword table. Are there any open-source projects that do this well for MySql?

Thanks,
Eliot

[[deleted]]

There is a drop-dead-simple keyword search in the articles section. It doesn't require a spider, just self-updating.

So far the FULLTEXT index in mysql has not proven to be very usefull in my humble opinion.

[eshepard]

Right, I just found the article. With a few improvements (eg. proximity scoring), it's probably possible to build something useful.

1) filter noise words
2) save keywords with a normalized [0,1] location value (and possibly a metaphone value)
3) run the search query on the table, scoring by proximity and frequency

I'll try that.

Eliot

[katzkek]

It's very possible... I've adapted one that does exactly what you just specced from examples from here and devshed. Note: The devshed code doesn't work right off...

I would write a manual spider (in case you somehow bork your index) in perl to start out with, and then add automatic keyword table updating to your input/edit scripts later.