1. define search index (via mysql query)
2. do actual index and setup reindex via cron (at least that how i did it - now seems to be some kind of relat time index update option)
3. connect to searchd via php api with query - resposn was object of document ids best matching search criteria
4. do mysql query with returning id's as where in parameter
5. sort results as returned form searchd api call
This thing actually works just fine but its a bit complicated and could be done much simpler. So after doin a bit of research for new project i found this document ( https://mariadb.com/kb/en/about-sphinxse/ ) describing searching for results within mysql.
Let's asume that we have working searchd with our index (tasks 1 and 2)
Latest versions of MariaDB apparently include sphinx plugin that can be easily activate by
MariaDB [(none)]> INSTALL PLUGIN sphinx SONAME 'ha_sphinx.so';
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> show engines;
+------------+---------+--------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+------------+---------+--------------------------------------------+--------------+------+------------+
| SPHINX | YES | Sphinx storage engine 0.9.9 | NO | NO | NO |
+------------+---------+--------------------------------------------+--------------+------+------------+
We can see plugin has been successfully running inside MySQL/MariaDB database.
Next thing would be to define local table with connection to searchd engine parameter
MariaDB [(none)]>CREATE TABLE t1
(
id INTEGER UNSIGNED NOT NULL,
weight INTEGER NOT NULL,
query VARCHAR(3072) NOT NULL,
group_id INTEGER,
INDEX(query)
) ENGINE=SPHINX CONNECTION="sphinx://127.0.0.1:9312/test1";
So inside out database we now have two tables -- one that is actually holding data* and the other "fake" table that connects to searchd and pull out results form our search query
MariaDB [nfo]> show tables;
+---------------+
| Tables_in_nfo |
+---------------+
| predb |
| t1 |
+---------------+
* i use 27 Years of Warez Scene Release Info Leaked in Giant Database ( http://torrentfreak.com/27-years-of-warez-scene-release-info-leaked-in-giant-database/ ) as my source - 2.6 mio records
So now we can achieve tasks 3,4 and 5 within mysql with just one query like this
MariaDB [nfo]> SELECT predb.id, rlsname, grp FROM t1 JOIN predb on t1.id = predb.id WHERE query='tie fighter';
+---------+-----------------------------------------------------------+---------+
| id | rlsname | grp |
+---------+-----------------------------------------------------------+---------+
| 2338491 | X-Wing.vs.Tie.Fighter.Balance.of.Power-HBD | HBD |
| 2338559 | TIE.Fighter.Collector.Series-iLLoGiC | iLLoGiC |
| 2338560 | TIE.Fighter.Collector.Series.Complete.Intro.Addon-iLLoGiC | iLLoGiC |
| 2338562 | TIE.Fighter.Collector.Series.Music.Addon-iLLoGiC | iLLoGiC |
| 2338565 | TIE.Fighter.Collector.Series.3-D.Fix-iLLoGiC | iLLoGiC |
| 2340174 | X-Wing.vs.Tie-Fighter-HBD | HBD |
+---------+-----------------------------------------------------------+---------+
6 rows in set (0.00 sec)
Final words/benchmarks
MariaDB [nfo]> SELECT id,rlsname FROM predb WHERE rlsname LIKE '%zaxxon%';
+---------+---------------------------------------------+
| id | rlsname |
+---------+---------------------------------------------+
| 804051 | Bassplugger-Zaxxon-ILEC006-Advance-2004-RFL |
| 1847329 | Zaxxon-DPS |
+---------+---------------------------------------------+
2 rows in set (1.79 sec)
MariaDB [nfo]> SELECT * FROM t1 WHERE query='zaxxon';
+---------+--------+--------+----------+
| id | weight | query | group_id |
+---------+--------+--------+----------+
| 804051 | 1 | zaxxon | 0 |
| 1847329 | 1 | zaxxon | 0 |
+---------+--------+--------+----------+
2 rows in set (0.01 sec)
MariaDB [nfo]> SELECT predb.id, rlsname, grp FROM t1 JOIN predb on t1.id = predb.id WHERE query='zaxxon';
+---------+---------------------------------------------+-----+
| id | rlsname | grp |
+---------+---------------------------------------------+-----+
| 804051 | Bassplugger-Zaxxon-ILEC006-Advance-2004-RFL | RFL |
| 1847329 | Zaxxon-DPS | DPS |
+---------+---------------------------------------------+-----+
2 rows in set (0.04 sec)
