mysql stored procedure for search from identical tables

Question

Hi developers I am researching ways of fast selecting rows from many tables with identical structure. Tables have too much data to be 1 table. So every table contains data for 1 month. For example log_2011_01 ,log_2011_02 etc. Tables have datetime column created_at.

I need to create stored procedure , it must contain 2 datetime parameters for searching between this dates and few others.

Now I see next realization: stored procedure must calculate collection of tables between 2 dates and generate sql to select data with union_all . I think I will be to heavy and not ready for highload,isn't it? Is there are any other possibilities to do this? Thanks

Answer 1

Agree with other comments, but I tried to help you with SP. This SP builds the query that can be executed with 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