Magento – Tables indexes – too little or too many

database

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)

Best Answer

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

Related Topic