MySQL export indices

Question

I'm sure it must have been asked before, but I can't find it:

I have a CMS that is under constant development. I have built a number of websites using the CMS, so their are a number of databases in existence.

I want to extract the indices from the development db and apply them to the production dbs. Is there an easy way to extract indices as SQL?

Something along the lines of:

create index idx1 on one (lft, rght);
create index idx1 on two (alias);
create index acos_idx3 on three (model, related_id);

Answer 1

I don't work with My SQL a whole lot, but as I remember, the backups were open text DDL statements. Seems like that would mean that the beginning of each backup would contain all of the tables, then all of the indexes.?

Maybe this command with a bit of table looping will get you want you want: My SQL 5.0 Reference Manual.

Good LUCK!

Answer 2

You can extract indices from INFORMATION_SCHEMA database, then add them on another database, but it's not quite easy.

To give you an example (code from a stored procedure used for deployment), this adds an unique key if it's not already there:

if not exists (select * from information_schema.KEY_COLUMN_USAGE 
                where table_schema = 'database_name' 
                and table_name='your_table'
                and constraint_name ='key_name')
then
    alter table your_table add unique key `key_name` ('column_name');
end if;

You can basically find whatever you need in INFORMATION_SCHEMA. I believe you can write code to dynamically check for all these indices, but I'm not sure if it's easy for you.

UPDATE:

You can also use show index from database.table, as you can see at the link provided by MaasSql's answer. Then loop throuth the results and add each index if it's not in the database.

Or you can try this:

if not exists (select * from information_schema.STATISTICS
where table_schema = 'database_name' 
and table_name='table_name'
and index_name ='key_name')
then 
...
end if;

Answer 3

Well, I had to move onto something else and this is as far as I got (just before Ceteras published his update!). The problem at the moment is that CREATE INDEX IF NOT EXISTS is invalid, so I need to find another way to do it. Might get back on this at the end of the week.

$query="SHOW TABLES";
$result = mysql_query($query);
while($row = mysql_fetch_array($result))
{
  $idxQ = "SHOW INDEXES FROM {$row[0]} IN $database;";
  $idxR = mysql_query($idxQ);

  while($idxRow = mysql_fetch_object($idxR))
  {
      //basic query: CREATE INDEX indexName ON tableName (columnName);
      $indexName = $idxRow->Key_name;
      if($indexName=='PRIMARY') continue;
      $tableName = $idxRow->Table;
      $columnName = $idxRow->Column_name;
      $idxCreateQ = "CREATE INDEX IF NOT EXISTS $indexName ON $tableName ($columnName);";
      echo $idxCreateQ;
  }

}