3 ms·

Why MySQL fulltext search WAS good enough

8 points by pornark 15y ago

rick888 15y ago
The problem with Mysql fulltext is that you are required to use the MyISAM table type. If you are doing any writes to this table, you will eventually run into corruption problems. This is because of the way it works underneath. So, I only use it with static data or data that I rarely update. I wish Innodb had fulltext search capabilities. I don't like the idea of adding yet another 3rd party server in the mix.
- kennu 15y ago
  You should use the right tool for the job. Sphinx is a very functional search product, is available as a Ubuntu package and is easy to integrate to e.g. Django data models. (The article links to some weird page in http://www.sphinxsearch.org/ http://www.sphinxsearch.org/. The right link is http://sphinxsearch.com/. http://sphinxsearch.com/.)
  - rick888 15y ago
    I've used it before. I agree. Sphinx works very well. Craigslist even switched over to it a couple of years ago.
  - regularfry 15y ago
    Sometimes you just can't. One of my first projects as a freelancer involved hacking full-text search onto a niche news site hosted on a shared host with a shared MySQL instance (specced before my time, and not something I could change). Sphinx wasn't available, Lucene certainly wasn't an option, and the immutable MySQL fulltext config wouldn't let words of 3 letters or less be indexed. However, that wasn't good enough, because there were a lot of important 3-letter company names in the niche. So, what did I do? I prepended 'zzz' to every word before it went into the fulltext index. Dirty, hacky, inefficient, and let me deliver working functionality the same day the request came in.
- saurik 15y ago
  Given that PostgreSQL has both fulltext search capability and a sane concurrency/durability model, this seems like a solved problem.
- quattrofan 15y ago
  I agree with the other posters Sphinx is the way to go, or you can go the route of pulling the data you need to search on out to a specific MyISAM table and leave everything else in INNODB but of course there is an overhead to that...

8 points by pornark 15y ago

The problem with Mysql fulltext is that you are required to use the MyISAM table type. If you are doing any writes to this table, you will eventually run into corruption problems. This is because of the way it works underneath. So, I only use it with static data or data that I rarely update. I wish Innodb had fulltext search capabilities. I don't like the idea of adding yet another 3rd party server in the mix.

kennu 15y ago
You should use the right tool for the job. Sphinx is a very functional search product, is available as a Ubuntu package and is easy to integrate to e.g. Django data models. (The article links to some weird page in http://www.sphinxsearch.org/ http://www.sphinxsearch.org/. The right link is http://sphinxsearch.com/. http://sphinxsearch.com/.)
- rick888 15y ago
  I've used it before. I agree. Sphinx works very well. Craigslist even switched over to it a couple of years ago.
- regularfry 15y ago
  Sometimes you just can't. One of my first projects as a freelancer involved hacking full-text search onto a niche news site hosted on a shared host with a shared MySQL instance (specced before my time, and not something I could change). Sphinx wasn't available, Lucene certainly wasn't an option, and the immutable MySQL fulltext config wouldn't let words of 3 letters or less be indexed. However, that wasn't good enough, because there were a lot of important 3-letter company names in the niche. So, what did I do? I prepended 'zzz' to every word before it went into the fulltext index. Dirty, hacky, inefficient, and let me deliver working functionality the same day the request came in.
saurik 15y ago
Given that PostgreSQL has both fulltext search capability and a sane concurrency/durability model, this seems like a solved problem.
quattrofan 15y ago
I agree with the other posters Sphinx is the way to go, or you can go the route of pulling the data you need to search on out to a specific MyISAM table and leave everything else in INNODB but of course there is an overhead to that...