1

I have database that contains only events history for account. This DB will have as many tables as registered accounts, but every table will have similar structure, so only difference is only in names of table. I wan to run query that will trim other logs except last N values.

According to this question I can schedule autorunning some query for single table.

Can I execute same query for every table in DB without manual setting it to every table?

__

I would like to explain why i want to make many tables instead of one big table - every account event log can contain hundreds of events. But this events are rarely changes by single account and rarely select. I really don't understand, why i need make selection through millions of rows, in case if there will be only one table for all accounts events to search, for single account data.
Community
  • 1
  • 1
Asprelis
  • 664
  • 2
  • 10
  • 26

1 Answers1

0

You can query for table names using this query:

SELECT * FROM information_schema.tables;

Read more about this here. Using the results of the above query in a stored procedure that is triggered by the mysql event scheduler should allow you to do all sorts of automated maintenance work.

NOTE:

I think you may have seriously misunderstood the concept of relational databases. Are you sure you need an own table for each registered account? This sounds very wrong to me.

luksch
  • 11,497
  • 6
  • 38
  • 53
  • my thouths was: 1) logs can be large, so i preffer to run dedicated logs database in order to shard it in future. 2) I will always know name of table so it must be much faster than filtering events by account in huge common table – Asprelis Jul 18 '15 at 15:05
  • I think you try to optimize too early. One thing with many tables may be that the indexes for each table may be bigger than the single index of a huge log table. Your approach may be valid, I can't tell really. I would advise however to start optimizing things when you run into problems with a clean in simple design. Not before that. – luksch Jul 18 '15 at 15:50
  • sorry, but i still cant get your answer. indormation_shema.tables contains ALL tables in DB. i need to separate from tables only those which table schema column equals is "my_schema" – Asprelis Jul 18 '15 at 16:12
  • It is a regular SELECT statement. You can JOIN and set WHERE clauses all you like. You can use this in DELETE and UPDATE statements as well... – luksch Jul 18 '15 at 17:36