What is a good database design (schema) for an article search engine application?
Posted by: ali aba
Date: June 26, 2014 01:56AM

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`)
)
COLLATE='utf8_general_ci'
ENGINE=MyISAM;

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.

Options: ReplyQuote


Subject
Written By
Posted
What is a good database design (schema) for an article search engine application?
June 26, 2014 01:56AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.