MySQL Forums
Forum List  »  Stored Procedures

Re: Stored Procedure to create table
Posted by: Phillip Ward
Date: July 14, 2022 02:03AM

Quote

... many tables, all the same fields, but the names must change based on the date.

I would strongly advise against doing this.

To anyone familiar with spreadsheet products, like MS Excel, this "Table-Per-Thing" Model seems a "reasonable" and "logical" choice.
To anyone that knows Databases, it is not.

The "Table-per-Thing" Model always breaks down at some point.

You can achieve a far better result by using a single table to which you add that qualifying datetime column. With proper indexing, you will get performance that's just as good as "Table-Per-Thing" promises but will give you far fewer headaches, in the long run.
For example if you needed to summarise your data across `n` years, then you'd need `12 * n` tables in the query - MySQL is [still] limited to only 61 tables in any one query (including those in any views). If you want more than five years of Data in this scenario, you just can't do it.

Properly indexed, through, a single table will easily handle millions of rows of data. There is simply no need to split it up like this.

Regards, Phill W.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Stored Procedure to create table
41
July 14, 2022 02:03AM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.