1, Sphinx can be MySQL’s storage engine

CREATE TABLE t1
(id INTEGER NOT NULL,
weight INTEGER NOT NULL,
query VARCHAR(3072) NOT NULL,
group_id INTEGER,
INDEX(query)
) ENGINE=SPHINX CONNECTION=”sphinx://localhost:3312/enwiki”;

SELECT * FROM enwiki.searchindex docs
JOIN test.t1 ON (docs.si_page=t1.id) InsertText
WHERE query=”one document;mode=any” limit 1;

2, How to configure Sphinx with MySQL

Sphinx engine/plugin is not full engine:
still need to run “searcher” daemon

Need to compile MySQL source with Sphinx to integrate it
MySQL 5.0: need to patch source code
MySQL 5.1: no need to patch, copy Sphinx plugin to plugin dir

Technorati : Sphinx MySQL Integration

ALTER TABLE tbl AUTO_INCREMENT = 100;

If you get an error message like this:

Table './labsupply/db_cache' is marked as crashed and should be repaired.

You can repair the table using the following steps:

1, Login to mysql

# mysql uroot -p

2, change database

mysql> use labsupply
Database changed

3, check table

mysql> check table db_cache;
+——————–+——-+———-+————————————————— ——-+
| Table | Op | Msg_type | Msg_text |
+——————–+——-+———-+————————————————— ——-+
| labsupply.db_cache | check | warning | Table is marked as crashed |
| labsupply.db_cache | check | warning | 3 clients are using or haven’t closed the table pr operly |
| labsupply.db_cache | check | status | OK |
+——————–+——-+———-+————————————————— ——-+
3 rows in set (0.03 sec)

4, repair table.

mysql> repair table db_cache;
+——————–+——–+———-+———-+
| Table | Op | Msg_type | Msg_text |
+——————–+——–+———-+———-+
| labsupply.db_cache | repair | status | OK |
+——————–+——–+———-+———-+
1 row in set (0.00 sec)