Menu
I have ~1000 tables that start with the same prefix :
table_prefix_{SOME_ID} (i can take the ids from another table)
what is the fast way to loop over all the tables in mysql and do :
ALTER TABLE `table_prefix_{some_id}` ADD INDEX `fields` (`field`)
329
In MySQL, an index can be created on a table when the table is created with CREATE TABLE command. Otherwise, CREATE INDEX enables to add indexes to existing tables. A multiple-column index can be created using multiple columns. The indexes are formed by concatenating the values of the given columns.
Any query which works on any single record taken from a table can be wrapped in a procedure to make it run through each row of a table like so: First delete any existing procedure with the same name, and change the delimiter so your SQL doesn't try to run each line as you're trying to write the procedure.
MySQL can use multiple-column indexes for queries that test all the columns in the index, or queries that test just the first column, the first two columns, the first three columns, and so on.
There is no ALTER INDEX command in MySQL. You can only DROP INDEX and then CREATE INDEX with the new name.
Forget looping. Just do this:
select concat( 'alter table ', a.table_name, ' add index `fields` (`field`);' )
from information_schema.tables a
where a.table_name like 'table_prefix_%';
Then take the result set and run it as a SQL script.
BTW, you probably mean create index index_name on table_name( column_name);
50
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
