MySql: Query multiple identical dynamic tables.

Question

I have a database with 500+ tables, each with identical structure, that contain historical data from sensors. I am trying to come up with a query that will locate, for example, all instances where sensor n exceeds x. The problem is that the tables are dynamic, the query must be able to dynamically obtain the list of tables.

I can query information_schema.tables to get a list of the tables, like so:

SELECT table_name
FROM information_schema.tables
WHERE table_schema = 'database_name';

I can use this to create a loop in the program and then query the database repeatedly, however it seems like there should be a way to have MySql do the multiple table search.

I have not been able to make a stored procedure that works, but the examples I can find are generally for searching for a string in any column. I want to specifically find data in a specific column that exists in all tables. I admit I do not understand how to properly use stored procedures nor if they are the appropriate solution to this problem.

An example query inside the loop would be:

SELECT device_name, sensor_value
FROM device_table
WHERE sensor_value > 10;

Trying the following does not work:

SELECT device_name, sensor_value
FROM
    (
    SELECT table_name FROM information_schema.tables WHERE table_schema = 'database_name'
    )
WHERE sensor_value > 10;

It results in an error: "Every derived table must have its own alias."

The goal is to have a list of all devices that have had a given sensor value occur anywhere in their log (table).

Ultimately, should I just loop in my program once I've obtained a list of tables, or is there a query structure that would be more efficient?

Answer 1

You could loop through all the tables to create a single query dynamically like this:

SELECT device_name, sensor_value FROM device_table WHERE sensor_value > 10
UNION
SELECT device_name, sensor_value FROM device_table2 WHERE sensor_value > 10
UNION
SELECT device_name, sensor_value FROM device_table3 WHERE sensor_value > 10;

Answer 2

You're going to have to create a Stored Procedure. You'll need a looping cursor that will go through each record in

SELECT table_name FROM information_schema.tables WHERE table_schema = 'database_name';

And then you'll want to build up a long SQL statment like the one provided by jbochi

@Statement = CONCAT(@Statement, " UNION SELECT device_name, sensor_value FROM " , @table_name , " WHERE sensor_value > 10 ");

Once the large query is built up you'll exectute it with

PREPARE stmt FROM @Statement;

EXECUTE stmt;

DEALLOCATE PREPARE stmt;

Note: I don't really know how to do the looping cursor, sorry

Answer 3

Ultimately to solve this issue I get results from the separate tables and do my loops and comparisons in code. Later, it was made more efficient to combine all the sensor data in one table and optimize said table with indexes, which previously had been missing.

The moral of the story is that proper table structure is key to eliminating a lot of coding headaches!