Mysql stored procedure for searching from identical tables

Question

Hi, developers. I’m learning how to quickly select rows from many tables with the same structure. The tables have too much data for 1 table. Thus, each table contains data for 1 month. For example, log_2011_01, log_2011_02, etc. The tables have a datetime created_at column.

I need to create a stored procedure, it must contain 2 datetime parameters to search between these dates and several others.

Now I see the following implementation: the stored procedure should calculate the collection of tables between 2 dates and generate sql to select data with union_all. I think I’ll be hard and not ready for a big load, right? Are there any other options for this? Thanks

Answer 1

Agree with the other comments, but I tried to help you with the SP. This SP builds a query that can be executed using prepared statements .

 -- SP definition: DELIMITER $$ CREATE PROCEDURE iterateMonths(IN fromDate DATE, IN toDate DATE) BEGIN DECLARE tempDate DATE; DECLARE query VARCHAR(1000); -- Query string, length might be increased SET tempDate = fromDate - INTERVAL (dayofmonth(fromDate) - 1) DAY; WHILE tempDate <= toDate DO IF query IS NOT NULL THEN SET query = concat(query, '\r\nUNION ALL\r\n'); ELSE SET query = ''; END IF; SET query = concat(query, 'SELECT * FROM log_', DATE_FORMAT(tempDate, '%Y_%m')); SET tempDate = tempDate + INTERVAL 1 MONTH; END WHILE; SELECT query; -- Output generated query END $$ DELIMITER ; -- Call the SP SET @fromDate = '2010-07-29'; SET @toDate = '2011-08-29'; CALL iterateMonths(@fromDate, @toDate); -- Output: SELECT * FROM log_2010_07 UNION ALL SELECT * FROM log_2010_08 UNION ALL SELECT * FROM log_2010_09 UNION ALL SELECT * FROM log_2010_10 UNION ALL SELECT * FROM log_2010_11 UNION ALL SELECT * FROM log_2010_12 UNION ALL SELECT * FROM log_2011_01 UNION ALL SELECT * FROM log_2011_02 UNION ALL SELECT * FROM log_2011_03 UNION ALL SELECT * FROM log_2011_04 UNION ALL SELECT * FROM log_2011_05 UNION ALL SELECT * FROM log_2011_06 UNION ALL SELECT * FROM log_2011_07 UNION ALL SELECT * FROM log_2011_08 

Answer 2

There is no alternative to a stored procedure. Highload will be there, given the structure of your tables.