Fulltext searching in InnoDb table.

InnoDb_fulltext.sql

-- MAIN INNODB TABLE FOR ARTICLES
CREATE TABLE `article` (
  `id` SMALLINT(5) UNSIGNED NOT NULL AUTO_INCREMENT,
	`title` VARCHAR(50) NOT NULL,
	`abstract` TEXT NOT NULL,
	`content` MEDIUMTEXT NOT NULL,
	`publish_date` DATETIME NULL DEFAULT NULL,
	PRIMARY KEY (`id`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB;

-- MYISAM FULLTEXT INDEX FOR ARTICLES
CREATE TABLE `fulltext_index_article` (
	`id` SMALLINT(10) UNSIGNED NOT NULL,
	`title` VARCHAR(50) NOT NULL,
	`abstract` TEXT NOT NULL,
	`content` MEDIUMTEXT NOT NULL,
	PRIMARY KEY (`id`),
	FULLTEXT INDEX `title` (`title`),
	FULLTEXT INDEX `abstract` (`abstract`),
	FULLTEXT INDEX `content` (`content`)
)
COLLATE='utf8_general_ci'
ENGINE=MyISAM;

delimiter //
CREATE TRIGGER `fulltext_delete_article`
AFTER DELETE ON `article`
FOR EACH ROW
BEGIN
	DELETE FROM `fulltext_index_article`
	WHERE `fulltext_index_article`.`id` = old.`id`;
END//
delimiter ;

delimiter //
CREATE TRIGGER fulltext_insert_article
AFTER INSERT
ON article
FOR EACH ROW
BEGIN
	INSERT INTO `fulltext_index_article` (`id`, `title`, `abstract`, `content`)
	VALUES (new.id, new.title, new.abstract, new.content);
END//
delimiter ;

delimiter //
CREATE TRIGGER fulltext_update_article
AFTER UPDATE
ON article
FOR EACH ROW
BEGIN
	UPDATE fulltext_index_article
	SET
		fulltext_index_article.id = new.id,
		fulltext_index_article.title = new.title,
		fulltext_index_article.abstract = new.abstract,
		fulltext_index_article.content = new.content
	WHERE fulltext_index_article.id = old.id;
END//
delimiter ;
-- you may also add triggers to prevent direct changes in index but I don't think is necessary


-- Clear and build fulltext index for all articles
TRUNCATE `fulltext_index_article`;
INSERT INTO fulltext_index_article (id, title, abstract, content)
	SELECT id, title, abstract, content
	FROM article;

-- Lets fulltext!
SELECT article.*, (
	10 * match(fulltext_index_article.title) against ('prostor kurz php')
	+ 2 * match(fulltext_index_article.abstract) against ('prostor kurz php')
	+ 1 * match(fulltext_index_article.content) against ('prostor kurz php')
) / 13 AS fulltext_score
FROM fulltext_index_article
INNER JOIN article ON (
	fulltext_index_article.id = article.id
)
HAVING fulltext_score
ORDER BY fulltext_score DESC;