0

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

11
  • 1
    The goal here is to create indexes on all of these columns of type date, timestamp where no index currently exists In a half of cases the index by single field makes no sense.
    – Akina
    Apr 21 '20 at 19:34
  • Did you calculate the implication of running such a script to create all those indexes, I mean, each index will increase dramatically the tablespace file size, other than block some processes during the index creation. Apr 21 '20 at 21:29
  • 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
    Apr 21 '20 at 22:06
  • @Akina in this case it makes sense.. just for reference - consider the use case similar to: dba.stackexchange.com/questions/211801/… Check out your comments .on your answer .. however I am using the query like this one here as it is faster.. pastebin.com/guiYHkT1
    – StixO
    Apr 21 '20 at 22:17
  • 1
    If your typical query is WHERE foo_id = 123 AND date BETWEEN..., then INDEX(foo) is not nearly as good as INDEX(foo_id, date).
    – Rick James
    Apr 28 '20 at 23:58
0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.