Tables indexes - too little or too many

Question

we have a Magento installation @ Mage 1.7. We've been around since 1.3 ...

What we are seeing more and more is that certain indexes appear that are no longer in Mage 1.7. For example we have an index IDX_OZSS_CORE_CACHE_EXPIRE_TIME on core_cache that does not exist in the latest install.

Now I have used the DB repair tool: http://www.magentocommerce.com/wiki/1_-_installation_and_configuration/db-repair-tool - it does ADD missing indexes, but does it remove unused ol dindexes etc?

So my question is: how would I clean my database setup to match the latest Magento install using all the right indexes? (adding missing,and removing obsolete)

Answer 1

Start by creating a plain Magento 1.7 installation. Then run (separately):

select * from information_schema.statistics where TABLE_SCHEMA='YOUR_MAGENTO_DB';
select * from information_schema.statistics where TABLE_SCHEMA='PLAIN_MAGENTO_DB';

Each query will list the indexes in each database. You can then create a script to drop missing indexes/add new

Answer 2

Please find updated version of DB tool here: http://www.magentocommerce.com/wiki/1_-_installation_and_configuration/db-repair-tool

It now also removes indexes that are not supposed to be in Magento