Planet MySQL Planet MySQL: Meta Deutsch Español Français Italiano 日本語 Русский Português 中文
Showing entries 1 to 2

Displaying posts with tag: fulltext indexes (reset)

My long-promised Sphinx post (part 1)
+0 Vote Up -0Vote Down
I gave a webinar several weeks ago about Grazr's infrastructure, lessons we learned about scaling and over-building. One thing that I noticed a lot of interest in was how we used Sphinx to not only improve our search, but releive the database of FULLTEXT indices, which were a performance issue for us.

The purpose of these posts are to give you an idea of how Sphinx works, any limitations it has, and how you can use for both search functionality, as well as freeing your database from having to use FULLTEXT. I think Sphinx is a great project, and want to share info that can help promote its use.

There is a lot of information on Sphinx to share, so I'm going to make this a multiple post, in parts. I'm already working on a book, and Sphinx information could be a small book of its own!

Grazr overview, from FULLTEXT to Sphinx

Grazr is a company who derives it's







  [Read more...]
FULLTEXT lesson/reminder of the day
+0 Vote Up -0Vote Down
I've been using MySQL fulltext indexes on a table where I keep a few varchar and one text column that is used for searches. I've had it defined as:

CREATE TABLE `items_text` (
  `item_id` bigint(20) NOT NULL,
  `fts` varchar(4) NOT NULL default 'grzr',
  `author` varchar(80) NOT NULL default '',
  `title` varchar(255) NOT NULL default '',
  `content` text NOT NULL,
  PRIMARY KEY  (`item_id`),
  FULLTEXT KEY `title` (`title`),
  FULLTEXT KEY `author` (`author`),
  FULLTEXT KEY `fts` (`fts`),
  FULLTEXT KEY `content` (`content`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8


One of my colleagues pointed out he was experiencing slow performance with this query:

select count(*) from items_text where (MATCH (title, author, content) AGAINST ('+iron +man' IN BOOLEAN MODE))


I ran EXPLAIN just to make sure that the index was being used:











  [Read more...]
Showing entries 1 to 2

Planet MySQL © 1995, 2014, Oracle Corporation and/or its affiliates   Legal Policies | Your Privacy Rights | Terms of Use

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.