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 searchable content from …

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:

mysql> explain …