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

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.)