MySQL with monthly tables and monthly joins

Question

I am an trainee as a software developer. I have a question: How can I access to multiple tables with select and join?

The name of the main-tables are like:

logs_0215
logs_0315

logs_mmyy  ->  m = month; y = year

The name of the timestamp-tables are like:

timestamp_0215
timestamp_0315

timestamp_mmyy  ->  m = month; y = year

My query which works only for one table is like:

SELECT a.id, b.stamp, c.name AS portal, d.name AS company, e.name AS protocol, a.value
FROM logs_0315 AS a
LEFT JOIN timestamp_0315 AS b ON a.time_id = b.id
LEFT JOIN portale AS c ON a.portal_id = c.id
LEFT JOIN companies AS d ON a.comp_id = d.id
LEFT JOIN proocols AS e ON a.prot_id = e.id
WHERE b.stamp BETWEEN '2015-05-01' AND '2015-06-25'

How can I access to all tables which contains logs_mmyy with its timestamp?

Thank you for your help in advance.

Yours sincerely
t.koelpin

you can't, not without dynamically building/executing query strings. your system SHOULD be redesigned to a SINGLE table which contains a `timestamp` field, from which you can trivially extract month/year information, e.g. `select count(*) from logs where year(timestamp)=2015 and month(timestamp)=2`. then it's also easy to let the DB engine partition your table for you, without having to deal with littering your db with pointless "duplicate" tables. — Marc B, Aug 10 '15 at 18:21
Why SHOULD I REDESIGN the table? What's the reason for? I decided the format, because the search-speed is with the one-table very slow. I decided to split the data into a monthly table for an higher performance. The one-table contained over 100.000.000 Rows! I just thought of a multiple SELECT with concatting the tables. Is this way possible? — t.koelpin, Aug 10 '15 at 18:35
that's what indexes are for, and server-level partitioning. there's no way to do something like `select * from time_*`, since wildcards and "dynamic" table/field names are not permitted, period, in queries. — Marc B, Aug 10 '15 at 18:44

spencer7593 · Accepted Answer · 2015-08-10T18:46:38.410

You would have to explicitly identify each table; each table has to be identified by name in a FROM clause. SQL does not support wildcard pattern matching for identifiers (table names, column names, etc.).

Given what you've posted, it looks like you could use a UNION ALL set operator to combine results from multiple SELECT statements as a single query:

  SELECT a.id, ...
    FROM logs_0315 a 
    LEFT JOIN timestamp_0315 AS b ON a.time_id = b.id
         ...
   WHERE b.stamp BETWEEN '2015-05-01' AND '2015-06-25'
  UNION ALL
  SELECT a.id, ...
    FROM logs_0415 a 
    LEFT JOIN timestamp_0415 AS b ON a.time_id = b.id
         ...
   WHERE b.stamp BETWEEN '2015-05-01' AND '2015-06-25'
  UNION ALL
  SELECT a.id, ...
    FROM logs_0515 a 
    LEFT JOIN timestamp_0515 AS b ON a.time_id = b.id
         ...
   WHERE b.stamp BETWEEN '2015-05-01' AND '2015-06-25'

If you need an ORDER BY on the combined result, you can enclose each SELECT statement in parens. Like this:

 ( SELECT a.id, ...
     FROM logs_0315 a 
     LEFT JOIN timestamp_0315 AS b ON a.time_id = b.id
          ...
    WHERE b.stamp BETWEEN '2015-05-01' AND '2015-06-25'
 )
 UNION ALL
 ( SELECT a.id, ...
     FROM logs_0415 a 
     LEFT JOIN timestamp_0415 AS b ON a.time_id = b.id
          ...
    WHERE b.stamp BETWEEN '2015-05-01' AND '2015-06-25'
 )
 UNION ALL
 ( SELECT a.id, ...
     FROM logs_0515 a 
      LEFT JOIN timestamp_0515 AS b ON a.time_id = b.id
          ...
    WHERE b.stamp BETWEEN '2015-05-01' AND '2015-06-25'
 )
 ORDER BY ...

In order to make this dynamic, to get all tables of the pattern logs_mmyy, you could query information_schema.tables and get the list of tables, and then use that to dynamically generate a statement as above. This approach requires (at a minimum) two separate SQL statements. It's not possible to achieve this type of result in a single SQL statement.

I hope this is the solution what I wanted. I'll testing it tomorrow. If it works, this thread is soluted then ... — t.koelpin, Aug 10 '15 at 18:45
This is the solution what I wanted for. In the combination with [link]http://stackoverflow.com/questions/2387734/a-simple-way-to-sum-a-result-from-union-in-mysql[/link] I solved the problem. Thank you so much! — t.koelpin, Aug 11 '15 at 06:32

MySQL with monthly tables and monthly joins

1 Answers1