Unanswered: What is a good database design (schema) for an article search engine application?
I'm trying to make a article search engine application. There are about 1 million articles that will be stored in the DB. I've tried looking around for some similar examples, but I couldn't find any specific or clear enough ones for this kind of application.
At the moment, I am using a MyISAM table that contains two fields: id, article. article field is fulltext. I use following SQL to publish results to users:
SELECT * FROM articles WHERE MATCH (article) AGAINST ('software engineering in europe');
This is create code of the table:
CREATE TABLE `articles` (
`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`article` TEXT NULL,
PRIMARY KEY (`id`),
FULLTEXT INDEX `article` (`article`)
Currently it works very slow. I am trying to make it so it is faster and it uses less resource. I've noticed sphinx is very popular for this kind of applications but if possible I really like to use only MySQL + PHP because of budget and simplicity reasons.Is there some other better approach to designing this kind of database? Thanks in advance.