Every client of our online services has its own MySQL database. All databases have exactly same schema, however, a number of databases is constantly changing as we archive db for inactive client's as well as new db gets created after every signup.
Is there a way to run analytics in this kind of environment? There seems to be no way to run query across multiple databases. The same applies to ETL solutions that we have investigated.
Merging databases and updating app code seems like a major effort. Create merged db for reporting purposes using db triggers might be the way to go.
Currently, if we need to run some reports across db, we use PHP script to execute query on every single client db which is very inefficient.
Any recommendations?
2 Answers 2
Not everything can be done in standard commands, or in complicated combinations of SQL.
I would write in my application coding language (PHP, Java, ...) SQL to fetch the list of databases from information_schema, then iterate through that list, doing the analytics.
Take a look here. Giuseppe Maxia is a well renowned MySQL expert. You should deal with the "disease" rather than firefight the symptoms.
What you are talking about is known as "multi-tenancy" and how to deal with it. Take a look here, here and here and links therein for discussions about the pros and cons of the different approaches to this (common) problem.
Again, I reiterate that you should tackle the underlying issue rather than cobbling together ad-hoc solutions which will strain and groan under the pressure of increasing numbers of schemas/users.
Possibly as a short term stopgap, you could use replication with triggers on the slave to consolidate your schemas and do analytics that way - just a thought!
IMHO, it's always best to get the database to do as much of the work as possible, while acknowledging Rick James' point that not everything is always possible through SQL, but triggers and SPs (Stored Procedures) can help.