0

I have a schema that is used to archive a data set on a daily basis. Some of the analysis needs to look back, so to optimise things I need to create a couple of indexes on each table. These would be seperate (I'm not trying to cross index or anything) just a simple non-unique index, but on each table in the schema.

The archive has already been building for over a year, so we have some 400 - 500 tables, making a manual ALTER query on each tablea bit too time consuming.

I could write a php script to do it, but wondered if there was a more elegant solution with a single query or transaction?

TIA

Shadow
  • 33,525
  • 10
  • 51
  • 64
quayfee
  • 1
  • 3
  • you need separate statements for each table and DDL statements are not part of transactions, so the answer is no to both parts of the question. You can generate the alter table statements in sql as well. – Shadow Sep 08 '21 at 09:33
  • Thanks Shadow, so something along the lines of `code` ALTER TABLE SELECT TABLE_NAME FROM information_schema.TABLES WHERE TABLE_SCHEMA = foo ADD INDEX bar (`col_name`) `code` ? – quayfee Sep 08 '21 at 09:42
  • Well, the alter table and add index sections will be string constants as you have to generate the alter table statements and then execute the alter table statements you generated in the first step. See an example here: https://stackoverflow.com/a/44527818/5389997 – Shadow Sep 08 '21 at 10:34
  • Ah! Yes, now I've got you! Neat! Thank you! – quayfee Sep 08 '21 at 10:53
  • Multiple tables with the same schema is usually a no-no. Did you consider `PARTITIONing`? – Rick James Sep 10 '21 at 05:19
  • Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. – Community Sep 13 '21 at 15:56

1 Answers1

0

I have copied @Shadow's answer in the comments above here to show it as the answer:

Well, the alter table and add index sections will be string constants as you have to generate the alter table statements and then execute the alter table statements you generated in the first step. See an example here: stackoverflow.com/a/44527818/5389997

quayfee
  • 1
  • 3