Mysql – How to select maximum date_created value from all tables matching certain pattern

dynamic-sqlinformation-schemaMySQL

Given a MySQL DB with a hundreds of tables with the ones of interest being named ap_form_* where * = a random value. How do I loop through that list of tables to display the MAX(date_created) for each individual table queried.

Best Answer

PROPOSED QUERY

SET group_concat_max_len = 1024 * 1024 * 10;
SELECT GROUP_CONCAT(OneSelectQuery SEPARATOR ' UNION ') INTO @LongQuery
FROM
(
    SELECT
        CONCAT('SELECT ',QUOTE(table_name),' tblname,',
        'MAX(date_create) MaxDateCreated FROM ',table_name) OneSelectQuery
    FROM information_schema.partitions
    WHERE table_schema=DATABASE()
    AND table_name LIKE 'ap_form_%'
    GROUP BY table_name
) A;
SELECT @LongQuery\G
PREPARE qry FROM @LongQuery;
EXECUTE qry;
DEALLOCATE PREPARE qry;

What this does is:

Gathered all tables from the database you ar in that has ap_form_% as a pattern
Create SELECT query for Each ap_form_% table
Concatenate all the Queries into a Single Query
Execute all the Queries as a Single Query

EXAMPLE

This does a table count of all tables in the mysql schema

USE mysql
SET group_concat_max_len = 1024 * 1024 * 10;
SELECT GROUP_CONCAT(OneSelectQuery SEPARATOR ' UNION ') INTO @LongQuery
FROM
(
    SELECT
        CONCAT('SELECT ',QUOTE(table_name),' tblname,',
        'COUNT(1) RowCount FROM ',table_name) OneSelectQuery
    FROM information_schema.partitions
    WHERE table_schema=DATABASE()
    GROUP BY table_name
) A;
SELECT @LongQuery\G
PREPARE qry FROM @LongQuery;
EXECUTE qry;
DEALLOCATE PREPARE qry;

EXAMPLE EXECUTED

MySQL [mysql]> USE mysql
Database changed
MySQL [mysql]> SET group_concat_max_len = 1024 * 1024 * 10;
Query OK, 0 rows affected (0.00 sec)

MySQL [mysql]> SELECT GROUP_CONCAT(OneSelectQuery SEPARATOR ' UNION ') INTO @LongQuery
    -> FROM
    -> (
    ->     SELECT
    ->         CONCAT('SELECT ',QUOTE(table_name),' tblname,',
    ->         'COUNT(1) RowCount FROM ',table_name) OneSelectQuery
    ->         FROM information_schema.partitions
    ->     WHERE table_schema=DATABASE()
    ->     GROUP BY table_name
    -> ) A;
Query OK, 1 row affected (0.01 sec)

MySQL [mysql]> SELECT @LongQuery\G
*************************** 1. row ***************************
@LongQuery: SELECT 'columns_priv' tblname,COUNT(1) RowCount FROM columns_priv UNION SELECT 'db' tblname,COUNT(1) RowCount FROM db UNION SELECT 'event' tblname,COUNT(1) RowCount FROM event UNION SELECT 'func' tblname,COUNT(1) RowCount FROM func UNION SELECT 'general_log' tblname,COUNT(1) RowCount FROM general_log UNION SELECT 'general_log_backup' tblname,COUNT(1) RowCount FROM general_log_backup UNION SELECT 'help_category' tblname,COUNT(1) RowCount FROM help_category UNION SELECT 'help_keyword' tblname,COUNT(1) RowCount FROM help_keyword UNION SELECT 'help_relation' tblname,COUNT(1) RowCount FROM help_relation UNION SELECT 'help_topic' tblname,COUNT(1) RowCount FROM help_topic UNION SELECT 'host' tblname,COUNT(1) RowCount FROM host UNION SELECT 'innodb_index_stats' tblname,COUNT(1) RowCount FROM innodb_index_stats UNION SELECT 'innodb_table_stats' tblname,COUNT(1) RowCount FROM innodb_table_stats UNION SELECT 'ndb_binlog_index' tblname,COUNT(1) RowCount FROM ndb_binlog_index UNION SELECT 'plugin' tblname,COUNT(1) RowCount FROM plugin UNION SELECT 'proc' tblname,COUNT(1) RowCount FROM proc UNION SELECT 'procs_priv' tblname,COUNT(1) RowCount FROM procs_priv UNION SELECT 'proxies_priv' tblname,COUNT(1) RowCount FROM proxies_priv UNION SELECT 'rds_configuration' tblname,COUNT(1) RowCount FROM rds_configuration UNION SELECT 'rds_global_status_history' tblname,COUNT(1) RowCount FROM rds_global_status_history UNION SELECT 'rds_global_status_history_old' tblname,COUNT(1) RowCount FROM rds_global_status_history_old UNION SELECT 'rds_heartbeat2' tblname,COUNT(1) RowCount FROM rds_heartbeat2 UNION SELECT 'rds_history' tblname,COUNT(1) RowCount FROM rds_history UNION SELECT 'rds_replication_status' tblname,COUNT(1) RowCount FROM rds_replication_status UNION SELECT 'rds_sysinfo' tblname,COUNT(1) RowCount FROM rds_sysinfo UNION SELECT 'servers' tblname,COUNT(1) RowCount FROM servers UNION SELECT 'slave_master_info' tblname,COUNT(1) RowCount FROM slave_master_info UNION SELECT 'slave_relay_log_info' tblname,COUNT(1) RowCount FROM slave_relay_log_info UNION SELECT 'slave_worker_info' tblname,COUNT(1) RowCount FROM slave_worker_info UNION SELECT 'slow_log' tblname,COUNT(1) RowCount FROM slow_log UNION SELECT 'slow_log_backup' tblname,COUNT(1) RowCount FROM slow_log_backup UNION SELECT 'tables_priv' tblname,COUNT(1) RowCount FROM tables_priv UNION SELECT 'time_zone' tblname,COUNT(1) RowCount FROM time_zone UNION SELECT 'time_zone_leap_second' tblname,COUNT(1) RowCount FROM time_zone_leap_second UNION SELECT 'time_zone_name' tblname,COUNT(1) RowCount FROM time_zone_name UNION SELECT 'time_zone_transition' tblname,COUNT(1) RowCount FROM time_zone_transition UNION SELECT 'time_zone_transition_type' tblname,COUNT(1) RowCount FROM time_zone_transition_type UNION SELECT 'user' tblname,COUNT(1) RowCount FROM user
1 row in set (0.00 sec)

MySQL [mysql]> PREPARE qry FROM @LongQuery;
Query OK, 0 rows affected (0.00 sec)
Statement prepared

MySQL [mysql]> EXECUTE qry;
+-------------------------------+----------+
| tblname                       | RowCount |
+-------------------------------+----------+
| columns_priv                  |        6 |
| db                            |      207 |
| event                         |        2 |
| func                          |        0 |
| general_log                   |        0 |
| general_log_backup            |        0 |
| help_category                 |       38 |
| help_keyword                  |      453 |
| help_relation                 |      992 |
| help_topic                    |      505 |
| host                          |        0 |
| innodb_index_stats            |    32532 |
| innodb_table_stats            |     3392 |
| ndb_binlog_index              |        0 |
| plugin                        |        0 |
| proc                          |       27 |
| procs_priv                    |        0 |
| proxies_priv                  |        1 |
| rds_configuration             |        1 |
| rds_global_status_history     |        0 |
| rds_global_status_history_old |        0 |
| rds_heartbeat2                |        1 |
| rds_history                   |        1 |
| rds_replication_status        |        1 |
| rds_sysinfo                   |        0 |
| servers                       |        0 |
| slave_master_info             |        0 |
| slave_relay_log_info          |        0 |
| slave_worker_info             |        0 |
| slow_log                      |        0 |
| slow_log_backup               |        0 |
| tables_priv                   |       48 |
| time_zone                     |     1768 |
| time_zone_leap_second         |        0 |
| time_zone_name                |     1768 |
| time_zone_transition          |   118021 |
| time_zone_transition_type     |     8611 |
| user                          |      138 |
+-------------------------------+----------+
38 rows in set (0.00 sec)

MySQL [mysql]> DEALLOCATE PREPARE qry;
Query OK, 0 rows affected (0.01 sec)

MySQL [mysql]>

This should work for you.

GIVE IT A TRY !!!

Related Solutions

Mysql – A question on MySQL SELECT Algorithm/Function

To see how MySQL is doing something you want to look at the execution plan. More information on MySQL Execution plans at http://dev.mysql.com/doc/refman/5.5/en/execution-plan-information.html . You should think of SQL as a declarative language, you tell it to do something , and it writes a program to do that. The execution plan shows you the program it wrote.

SQL will be able to look up specific entries when there is an index on the column ( like an index from a book). However, a binary field with a split distribution is a classic example of a column that doesn't have good selectivity, in other words, it isn't going to narrow down the search very much.

Mysql – How to select using the same fields from an undetermined number of tables using MySQL

You can have the information_schema build the entire query for you and execute it as Dynamic SQL (Prepared Statements as @a1ex07 first mentioned):

SELECT CONCAT('SELECT * FROM (SELECT bar_id,bar_weight FROM ',
GROUP_CONCAT(tb SEPARATOR
' WHERE foo_id=1 UNION SELECT bar_id,bar_weight FROM '),
' WHERE foo_id=1) as res order by res.bar_weight desc limit 0, 5')
INTO @foo_query FROM
(
    select CONCAT(table_schema,'.',table_name) tb
    from information_schema.tables
    where table_name like 'foo_reference%'
) A;
SELECT @foo_query\G
PREPARE stmt FROM @foo_query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

First, let's make some sample tables with sample data:

DROP DATABASE IF EXISTS timpost1;
DROP DATABASE IF EXISTS timpost2;
CREATE DATABASE timpost1;
CREATE DATABASE timpost2;
use timpost1
CREATE TABLE cookie_cutter
(
    foo_id int,
    bar_id int,
    bar_weight int,
    primary key (foo_id)
) ENGINE=MyISAM;
CREATE TABLE timpost1.foo_reference1 LIKE timpost1.cookie_cutter;
CREATE TABLE timpost1.foo_reference2 LIKE timpost1.cookie_cutter;
CREATE TABLE timpost1.foo_reference3 LIKE timpost1.cookie_cutter;
CREATE TABLE timpost2.foo_reference1 LIKE timpost1.cookie_cutter;
CREATE TABLE timpost2.foo_reference2 LIKE timpost1.cookie_cutter;
CREATE TABLE timpost2.foo_reference3 LIKE timpost1.cookie_cutter;
INSERT INTO timpost1.foo_reference1 VALUES (1,17,10),(2,3,20);
INSERT INTO timpost1.foo_reference2 VALUES (1,27,90),(2,3,20);
INSERT INTO timpost1.foo_reference3 VALUES (1,37,40),(2,3,20);
INSERT INTO timpost2.foo_reference1 VALUES (1,47,70),(2,3,20);
INSERT INTO timpost2.foo_reference2 VALUES (1,57,20),(2,3,20);
INSERT INTO timpost2.foo_reference3 VALUES (1,67,50),(2,3,20);

Here are the sample tables being made and loaded:

mysql> DROP DATABASE IF EXISTS timpost1;
Query OK, 4 rows affected (0.03 sec)

mysql> DROP DATABASE IF EXISTS timpost2;
Query OK, 3 rows affected (0.02 sec)

mysql> CREATE DATABASE timpost1;
Query OK, 1 row affected (0.00 sec)

mysql> CREATE DATABASE timpost2;
Query OK, 1 row affected (0.00 sec)

mysql> use timpost1
Database changed
mysql> CREATE TABLE cookie_cutter
    -> (
    ->     foo_id int,
    ->     bar_id int,
    ->     bar_weight int,
    ->     primary key (foo_id)
    -> ) ENGINE=MyISAM;
Query OK, 0 rows affected (0.03 sec)

mysql> CREATE TABLE timpost1.foo_reference1 LIKE timpost1.cookie_cutter;
Query OK, 0 rows affected (0.05 sec)

mysql> CREATE TABLE timpost1.foo_reference2 LIKE timpost1.cookie_cutter;
Query OK, 0 rows affected (0.05 sec)

mysql> CREATE TABLE timpost1.foo_reference3 LIKE timpost1.cookie_cutter;
Query OK, 0 rows affected (0.05 sec)

mysql> CREATE TABLE timpost2.foo_reference1 LIKE timpost1.cookie_cutter;
Query OK, 0 rows affected (0.06 sec)

mysql> CREATE TABLE timpost2.foo_reference2 LIKE timpost1.cookie_cutter;
Query OK, 0 rows affected (0.05 sec)

mysql> CREATE TABLE timpost2.foo_reference3 LIKE timpost1.cookie_cutter;
Query OK, 0 rows affected (0.05 sec)

mysql> INSERT INTO timpost1.foo_reference1 VALUES (1,17,10),(2,3,20);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> INSERT INTO timpost1.foo_reference2 VALUES (1,27,90),(2,3,20);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> INSERT INTO timpost1.foo_reference3 VALUES (1,37,40),(2,3,20);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> INSERT INTO timpost2.foo_reference1 VALUES (1,47,70),(2,3,20);
Query OK, 2 rows affected (0.02 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> INSERT INTO timpost2.foo_reference2 VALUES (1,57,20),(2,3,20);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> INSERT INTO timpost2.foo_reference3 VALUES (1,67,50),(2,3,20);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql>

Now let's build the query and execute it:

mysql> SELECT CONCAT('SELECT * FROM (SELECT bar_id,bar_weight FROM ',
    -> GROUP_CONCAT(tb SEPARATOR
    -> ' WHERE foo_id=1 UNION SELECT bar_id,bar_weight FROM '),
    -> ' WHERE foo_id=1) as res order by res.bar_weight desc limit 0, 5')
    -> INTO @foo_query FROM
    -> (
    ->     select CONCAT(table_schema,'.',table_name) tb
    ->     from information_schema.tables
    ->     where table_name like 'foo_reference%'
    -> ) A;
Query OK, 1 row affected (0.02 sec)

mysql> SELECT @foo_query\G
*************************** 1. row ***************************
@foo_query: SELECT * FROM (SELECT bar_id,bar_weight FROM timpost1.foo_reference1 WHERE foo_id=1 UNION SELECT bar_id,bar_weight FROM timpost1.foo_reference2 WHERE foo_id=1 UNION SELECT bar_id,bar_weight FROM timpost1.foo_reference3 WHERE foo_id=1 UNION SELECT bar_id,bar_weight FROM timpost2.foo_reference1 WHERE foo_id=1 UNION SELECT bar_id,bar_weight FROM timpost2.foo_reference2 WHERE foo_id=1 UNION SELECT bar_id,bar_weight FROM timpost2.foo_reference3 WHERE foo_id=1) as res order by res.bar_weight desc limit 0, 5
1 row in set (0.01 sec)

mysql> PREPARE stmt FROM @foo_query;
Query OK, 0 rows affected (0.01 sec)
Statement prepared

mysql> EXECUTE stmt;
+--------+------------+
| bar_id | bar_weight |
+--------+------------+
|     27 |         90 |
|     47 |         70 |
|     67 |         50 |
|     37 |         40 |
|     57 |         20 |
+--------+------------+
5 rows in set (0.00 sec)

mysql> DEALLOCATE PREPARE stmt;
Query OK, 0 rows affected (0.00 sec)

mysql>

CAVEAT

Notice that the query will cross check all databases in the mysql instance that starts with foo_reference. In the above example, both timpost1 and timpost2 were checked.

Also, notice that the subquery will get 6 rows, and the limit 0,5 properly displays the first 5 rows of the subquery.

Give it a Try !!!