Mysql – Selecting from all databases from multiple tables

MySQLphpmyadmin

I've got over 2000 databases that may or may not have the same tables, but in the cases where they do, they should have the same type of information. From one table (settings) I'm trying to get the rows that have the names:

email
client_id
client_name

Then from a second table (Cluster) I'm trying to just add up the number of rows.

I'm stuck at even getting just the email for all of them. Trying to do the following with every single database listed, it tells me it can only join a certain number at once. I'm not opposed to running the command multiple times to eventually get the information, but then it tells me that "name" is too ambiguous.

SELECT * 
FROM  `database1`.`settings`,
`database2`.`settings`,
`database3`.`settings`,
`database4`.`settings`
WHERE (
CONVERT(  `name` 
USING utf8 ) LIKE  '%email%'
)
LIMIT 0 , 30

Ideally, I want to be able to have email, client_id, client_name, and the Cluster row totals associated so I can import it into a single database later.

Any help is appreciated!

Best Answer

I would start by using information_schema.TABLES and COLUMNS to find which databases (table_schema) has what tables with the desired columns.

Then I would construct (via SELECT CONCAT("SELECT ", ...)...) the SELECT statements that contain references to all the tables/columns desired.

From them, copy and paste to run them. Or put all of this in a Stored Procedure so that a single CALL can do all the work including executeing the constructed SELECTs.

Related Solutions

MySQL partitioned tables

Partitioning could quite possibly help you in this situation. By putting the data into a partition, the database will have a smaller table for each client to deal with. This could help performance a lot.

Here is an example of how it would help. The data in the table would otherwise be interleaved, so different clients would have rows on a data page. Reading the data from a single client would then "clutter up" the available page cache with records from other clients.

However, it will not help very much if all the clients are accessing data concurrently, so all are competing for available memory. Partitioning probably will not make this worse, but it won't necessarily help.

Also, MySQL automatically partitions the indexes on the table, which is generally a good thing.

Often partitioning is applied to a date field, so older data gets put into less used partitions. This may be a case where another field is useful. But, if all the clients need all their data at the same time, then the partitioning may not give you much of a gain.

MySQL command “SHOW DATABASES” returns invalid databases

You need to make sure they do not contain any tables

Please run this query

select 
    table_schema AS BlankDatabases 
from
    (select 
        A.table_schema, COUNT(B.table_schema) table_count 
     from
        (select 
            schema_name AS table_schema
        from 
            information_schema.schemata
        where 
            schema_name not in ('information_schema','mysql','performance_schema')
        ) A
        left join
        (select 
            table_schema
        from 
            information_schema.tables
        where 
            table_schema not in ('information_schema','mysql','performance_schema')
        ) B
        using (table_schema)
    group by 
        A.table_schema
    ) AA 
where 
    table_count = 0;

This will tell you which databases do not have any tables

For example, let's say you run that query and get the following output

mysql> select table_schema BlankDatabases from
    -> (select A.table_schema,COUNT(B.table_schema) table_count from
    -> (select schema_name table_schema
    -> from information_schema.schemata
    -> where schema_name not in
    -> ('information_schema','mysql','performance_schema')) A
    -> left join
    -> (select table_schema
    -> from information_schema.tables
    -> where table_schema not in
    -> ('information_schema','mysql','performance_schema')) B
    -> using (table_schema)
    -> group by A.table_schema
    -> ) AA where table_count = 0;
+----------------+
| BlankDatabases |
+----------------+
| junk           |
| test           |
+----------------+
2 rows in set (0.00 sec)

mysql>

You can just run

DROP DATABASE junk;
DROP DATABASE test;

In your case, you should surround the database names with back quotes (`)

DROP DATABASE `@feff...@feff`;

If you are having a hard time, go to the OS and remove the folder.

For example, if your datadir is /var/lib/mysql and you are removing @feff...@feff, do this

cd /var/lib/mysql
rm -rf "@feff...@feff"

Best Answer

Related Solutions

MySQL partitioned tables

MySQL command “SHOW DATABASES” returns invalid databases

GIVE IT A TRY !!!

Related Question