MySQL How to Script to create indexes on all date columns in database

Question

I have a lot of tables in the database that are using a datetime stamp - I need to create a script that will create indexes on all columns in each table that does not already have an index .

So far I can get all the columns of type date, time etc.. I can get all of the existing indexes .. but I am having trouble combining them , as I intend to

Select (CONCAT('ALTER TABLE ' , table_name , ' ADD INDEX (' , column_name , ');') FROM 
MyIdentifiedColumns ..

The goal here is to create indexes on all of these columns of type date, timestamp where no index currently exists with a script - NOT manually in workbench .. So how can I do this ?

Get my Columns in the database that are of the proper date time

select col.table_schema as database_name,
 col.table_name,
 col.ordinal_position as column_id,
 col.column_name,
 col.data_type,
 col.datetime_precision
from information_schema.columns col
join information_schema.tables tab on tab.table_schema = col.table_schema
 and tab.table_name = col.table_name
 and tab.table_type = 'BASE TABLE'
where col.data_type in ('date', 'time', 'datetime', 'timestamp')
 and col.table_schema not in ('information_schema', 'sys',
 'performance_schema', 'mysql')
 and col.table_schema = 'mydatabase' 

And to get all of the Indexes currently in the database

select index_schema,
 index_name,
 group_concat(column_name order by seq_in_index) as index_columns,
 index_type,
 case non_unique
 when 1 then 'Not Unique'
 else 'Unique'
 end as is_unique,
 table_name
from information_schema.statistics
where table_schema not in ('information_schema', 'mysql',
 'performance_schema', 'sys') AND index_schema = 'mydatabase'
group by index_schema,
 index_name,
 index_type,
 non_unique,
 table_name
order by index_schema,
 index_name

• StixO: I understand there are implications of indexes - these are historical data records and they get updated with new records and each new record has a new date.now() so the record is at the end. The data is queried based on the date range - not by Primary key or any other fields nothing else. So if you had a table where you insert datetime and then 2 other values and say give me x from date to date .. I just want this script so I can automate adding those indexes on all of these tables ..
• StixO: In this case, it makes sense. Consider the use case similar to: dba.stackexchange.com/questions/211801/…. I am using the query like this one here as it is faster. pastebin.com/guiYHkT1

Answer 1

The larger the table, the longer the ADD INDEX will take, unless you have a current version, which has a rapid add index.

Your code seems promising. You could complete it by building a Stored Procedure to loop through the constructed ALTER TABLE commands to perform+execute them.

But first, run the stored routine to just SELECT the constructed ALTERs. Desk check the output. (And/or show us the output.)