I have mysql database with single host and a few hundred schemas, where each schemas have identical tables (and different amount of rows). What I want to do here is to query some (or all) schemas with same list of queries, and save these result for later use.
I'm using golang's database/sql
for querying database, and I want to do that with single connection pool, and by querying each schemas in parallel (hoping it's faster that way).
I'm imagining something like this:
func QuerySchemas(queryList []string, schemaList []string) {
// Create a connection pool
db, err := sql.Open("mysql", "user:password@tcp(db_host)/")
if err != nil {
log.Fatal(err)
}
for _, schema := range schemaList {
// Query each schema in parallel, probably with goroutine
go func(targetSchema string) {
// ...
// Let database/sql use targetSchema for this goroutine
// ...
for _, query := range queryList {
rows, err := db.Query(query)
// ...
// Save results and do other things
// ...
}
}(schema)
}
}
But I can't find how to let database/sql
query different schemas in parallel. So far I have thought of using MySQL's USE
sentences, but that would not work as it changes schema for all goroutines.
Is there any possible solutions?