0

I wonder if there is any way to keep main database clean of any Sphinx related tables?

Details: I have a database indexed by Sphinx. Being pretty big I used main + delta indexing recipe but in order to achieve that I had to add additional tables for counters and watchlists.

Right now I wonder if it is possible to keep the counters and watchlists tables in a separate database altogether and keep the main database as clean as possible.

Alex
  • 5,510
  • 8
  • 35
  • 54

2 Answers2

2

There is one connection. If you want to collect data from another DB, you could use federated tables (it can be slow with big tables, better could be a script that collects the data from all and feed Sphinx as XML/CSV), but you still need to create tables in existing DB so it defeats your purpose of having the DB 'clean'. Why not put those counter and watchlists tables in another DB on the same MySQL server?

aditirex
  • 692
  • 3
  • 12
  • This could be a solution but how to run queries against two databases inside a "sql_query" declaration when you can access a single one? – Alex Nov 29 '16 at 14:26
0

Certainly, you probably need to use the same creditentials to connect, so as long as the user can read from both dbs.

sql_query = select * from maindb.table where id > (select counter from othersb.sphinxcounter)
barryhunter
  • 20,886
  • 3
  • 30
  • 43
  • How does Sphinx know that "maindb" and "othersb" are even database names? As far I know Sphinx deals with "sources" which are practically connections to a single database. – Alex Nov 23 '16 at 08:43
  • Sphinx doesn't! Sphinx just runs the database queries. Its up to the database engine to route. For mysql, see the specifications on 'table_definition' http://dev.mysql.com/doc/refman/5.7/en/join.html The `sql_db` in sphinx, is just the 'default database' for the connection. Analgous to using the 'USE' sql command https://dev.mysql.com/doc/refman/5.7/en/use.html says 'Making a particular database the default by means of the USE statement does not preclude you from accessing tables in other databases.' – barryhunter Nov 23 '16 at 10:40
  • I want to have the second database ('othersb' in your example) to be on a different IP/server thus the credentials will be different. Is it still possible? – Alex Nov 28 '16 at 11:30
  • Hmm, that is much harder. I can only think to use something like http://dev.mysql.com/doc/refman/5.7/en/federated-storage-engine.html OR https://mariadb.com/kb/en/mariadb/connect-table-types-proxy-table-type/ – barryhunter Nov 28 '16 at 13:23