I am looking at converting out current MySQL database over to MariaDB 5.5 with SphinxSE. I haven't been able to find any real basic "yeah, you're an idiot" tutorials on using SphinxSE that's shipped with MariaDB.
I understand the first part of this tutorial from AskMonty on how to install the plugin, but then it starts into the basic usage and that's where my brain starts going in brain-fart mode. I understand the need for the special table created but it leaves me some questions at the end. In the example it says that id, weight, and query are required and they need specific settings. However, the following additional fields that you can have in that table don't necessarily make sense. Is this special table built dynamically when MySQL makes a query to Sphinx's searchd and the results are returned to this table or does this table hold a gazillion keywords populated by searchd?
According to this documentation about Sphinx (and among a few articles I found that were helpful) I wasn't really sure what was "taken care of" by SphinxSE and what I still needed to setup. I assume that I need to still setup indexes in the sphinx.conf file? Or are they somehow setup in the special table needed through the attributes passed through by query?
So now that you are aware of how confused I am, here's basically what's I'd like some help with. Let's pretend I just successfully installed MariaDB and I installed the SphinxSE plugin that's included. In my imaginary database I have 3 tables that I'd like to use the full-text search capabilities of Sphinx with.
--
-- Table structure for table `page_articles`
--
CREATE TABLE IF NOT EXISTS `page_articles` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`page_id` int(11) NOT NULL,
`body` longtext NOT NULL,
PRIMARY KEY (`id`),
KEY `page_id` (`page_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
-- --------------------------------------------------------
--
-- Table structure for table `page_comments`
--
CREATE TABLE IF NOT EXISTS `page_comments` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`user_id` int(11) NOT NULL,
`comment` text NOT NULL,
PRIMARY KEY (`id`),
KEY `user_id` (`user_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
-- --------------------------------------------------------
--
-- Table structure for table `page_content`
--
CREATE TABLE IF NOT EXISTS `page_content` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`title` varchar(100) NOT NULL,
`content` longtext NOT NULL,
`meta_keywords` text NOT NULL,
PRIMARY KEY (`id`),
FULLTEXT KEY `content` (`content`,`meta_keywords`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
What do I do from this point to properly setup the indexes I need? Like I said I read over several articles and it wasn't super clear on how fields are chosen to index, or if you only choose the MySQL full-text index and that index chooses the fields for you? After I have some of the indexes setup properly, how would I go about starting with some search queries I could use to pull data from these tables? I understand the syntax for a normal MySQL full text search (SELECT ... FROM table MATCH(blah_index) AGAINST (keywords)) but I don't know how or if it is even still used in the SphinxSE version. Any additional help would be greatly appreciated. Thanks.
SphinxSE is just an interface that lets you query a sphinx server using mysql. You still have to set up sphinx separately.
This article shows how to use the interface: Using SphinxSE
However note this part of the table definition: CONNECTION="sphinx://localhost:9312/test";
That's where you tell it to connect to your existing sphinx server. There is lots of documentation that explains how to set up sphinx. In fact there's even an article linked on the sphinx website which walks you through setting everything up to work with MySQL: Sphinx & MySQL