indexing text in databases

[stubarny]

Hi,

I'm trying to code a search query for a database of about 500,000 job adverts (similar advert length to monster, careerbuilder etc).

I have found this search code (http://iamcal.com/publish/articles/php/search/) which is suitable for 'small' tables. However at the bottom of the article it mentions that using a FULLTEXT index in mysql would be effective for medium sized databases. I'm assuming that 500,000 adverts is a 'medium' database so I'm thinking this could be a good solution, without having to get to grips with specialist indexing software (e.g. Zend Search Lucene - http://framework.zend.com/manual/en/zend.search.html)

Please can you give me your opinions on whether or not you think simply using a FULLTEXT index is a good solution? (I'm only a 'keen amateur' php/sql programmer so i don't have much experience with the practicalities of running largish databases)

Thanks a lot for your your time!

Stewart

[jheitz]

I would definetely go for FULLTEXT first. The best thing to do is to actually try it and see what response times you get, but I wouldn't consider a table with 500K records to be especially large.

Read article: http://www.databasejournal.com/features/mysql/article.php/1578331 for a good explanation on how to use FULLTEXT.

Good luck.

[chyan]

first, you had to check what table type do you've used, since fulltext can only work with MyISAM table with char,varchar or text column

for complete explanation u could refer to mysql manual at section 6.8 MySQL Full-Text Search,
good luck 🙂