However, I want to integrate Sphinx with my MySQL server so that all clients connecting to my sql server can do SphinxQL
I can’t do it. Sphinx (when it is enabled for sphinxQL) just gives you a server that looks like mysql, that is, it uses the same communication protocol - this is basically why it can just reuse the mysql client libraries, instead of creating a new one for the sphinx only .
These are different "servers". You connect to the mysql server to run mysql commands; you connect to the sphinx server to run sphinxQL commands.
The woudl application must be connected to each "server" separately. Just imagine that the sphinx was something like postgres, you are clearly not connecting to mysql and expect to be able to run postgresql.
However, there is SphinxSE - it is a fake mysql storage engine. You install it in mysql, and then you can create a table using this engine. Then you run mysql queries to this table, under the hood there are contacts with the sphinx server running. Thus, for mysql it looks like a table containing data, this is most useful because then you can "join" this search table with the original data table to get the results and source data in one mysql query.
Then the application should not connect to sphinx itself. SphinxSE does it for you.
http://sphinxsearch.com/docs/current.html#sphinxse
Do I need to convert a database engine from InnoDB to Sphinx?
Not. You keep the original data where it is, using what you like. Sphinx simply provides an “index” - it does not store the raw data *. Its not a database as such, it just provides a quick query with its optimized indexing.
Basically you are querying sphinx for a unique identifier for documents matching a specific query. Then use these identifiers to search for data. SphinxAPI, sphinxSE and sphinxQL are just three different mechanisms for doing this.
Also, from what it seems, Sphinx can only index one database table (1) for each configuration,
Not. A single sphinx instance may contain multiple indexes. And an index can have many sources. Thus, you can simply create one index for each table. Or, if you basically want to find them together, you can simply create one combined index.
- ** Edit to answer the question in the comments: **
When you say that sphinx can contain many indexes, does it rely solely on the sphinx.conf configuration file?
You would probably specify one sphinx index for each table. Thus, for each table, you need a pair of sources / indexes. (if you do not want to index all tables in one index, which is also possible.
It is not possible to read the tables themselves and create a configuration file; you must define each index separately.
When you say "gives you a server similar to mysql one," you mean as a proxy,
Not. Not a proxy.
where can my MySQL client connect to this Sphinx port and the client will think its MySQL server?
I guess, yes. The client will connect to it the same way it connects to the mysql server.
If so, can I execute both MySQL SQL queries and SphinxQL in the same connection?
Not. Impossible. Connect to mysql server to start mysql queries. Connect to a search to run sphinxQL queries.
Two connections, one per server.
why my MySQL Workbench cannot connect to port 9306,
I do not know. There may be a problem with the firewall.