Sphinx search: getting rt indexing to work with mysql - first time

Question

I am trying to get rt indexing to work: http://sphinxsearch.com/docs/current.html#rt-overview

I am missing the link between sphinx and mysql.

In sphinx.conf I have:

index rt_test
{
        type = rt
        path = /home/my/path/sphinx/data/rt_test
        rt_field = title
        rt_field = content
}

I run /home/path/bin/indexer --all

It tells me

skipping non-plain index 'rt_test'... (which I read is as it should be)

Then in mysql (logging in as I normally would):

create table rt_test(id INTEGER PRIMARY KEY NOT NULL AUTO_INCREMENT, title varchar(100),
content varchar(100));

insert into rt_test(title, content) values ("test title", "test content");

SELECT * FROM rt_test WHERE MATCH('test');

This gives me a "wrong syntax" error. That's not surprising. Mysql just thinks that I have created a regular table and inserted regular data, and now it doesn't understand the Sphinx query.

So what's the missing link? How does mysql know about sphinx? If I don't create the table first then I get an error that the table doesn't exist (sphinx has not made a "sphinx" table to be queried from mysql).

I have installed sphinx on Linux as described here: http://sphinxsearch.com/docs/current.html#installing

Using this version:

wget http://sphinxsearch.com/files/sphinx-2.0.8-release.tar.gz

Edit: I also ran $searchd

It says:

WARNING: compat_sphinxql_magics=1 is deprecated; please update your applica
WARNING: preopen_indexes=1 has no effect with seamless_rotate=0
listening on all interfaces, port=9312
listening on all interfaces, port=9306
precaching index 'other'
precaching index 'rt_test'
precached 2 indexes in 0.012 sec

Answer 1

I get it now.

I don't log into my regular mysql database, but (funny enough) do as it says:

$ mysql -h 127.0.0.1 -P 9306

And then I don't create the table. Just insert and search.

My regular database and the sphinx mysql are completely separate. I have to insert all my data into my regular database and also into the sphinx database. Then search in the sphinx database and use the result to get the full data in my regular database.