Mysql – Can creating indexes colide with future database upgrades

indexMySQL

I have a mysql Database with very slow querys. I am using the slow query log to monitor those.

This is a database created by a third party software that we're using. Their developers didn't do any kind of optimization at this level on the database. Some indexes would certainly improve performance.

My main concern is that my indexes may collide with future database upgrades (structural or index creation).

By nature indexes are not invasive in the DB structure/data, but upon table change they may? prevent these from happening successfully.

Can I add indexes safely, with the certainty that they will be handled (maybe even dropped) if changes need to be done, or should I avoid that?

Best Answer

What I would do is maintain a SQL script for the indexes that you've added to the "virgin" database. Store this script in a version control system, as you'll be adding indexes to fix performance problems.

I would employ a naming strategy to make it easy to identify which indexes are "yours" and not part of the database as delivered. I'm not sure what features MySQL has, but in Oracle for example you can store these indexes in a different tablespace making it very easy to identify them as "added".

When your vendor supplies an upgrade, first remove your added indexes, then apply your vendor's upgrade and afterwards execute your script to add your indexes.

Of course, you should also have a QA environment where you can test all of this before applying it to your production system. You'll need to do this to discover if any indexes will conflict with indexes that the vendor may create in a future version.

Related Solutions

Creating Indexes to optimize query

Perhaps this will help. If your data looks something like this:

create table mytab1 as (select level myid1 from dual connect by level <=2000000);
create table mytab2 as (select level+20000 myid2 from dual connect by level <=100);

No indexes:

Cost: 881, Consistent Gets: 3140, CPU Used: 174

Index on MyTab1 Table:

create index myid1_1 on mytab1 (myid1);

Cost: 104, Consistent Gets: 103, CPU Used: 14

Index on both MyTab1 and MyTab2:

create index myid2_1 on mytab2 (myid2);

Cost: 53, Consistent Gets: 102, CPU Used: 12

Your mileage will vary depending on quite a few factors, but in general an index on myid1 would probably be beneficial.

If you haven't already you should read through the Oracle Concepts Guide (pdf) particularly the section on indexing.

Creating Bitmap Indexes on a Materialized View

The key phrase is "after batch row loading". If your process does not meet this criteria then you do not need to drop/rebuild the bitmap indexes.

Jonathan Lewis has an excellent article on this. A key point he gives is

You can get lucky -- but in general you should start with the assumption that even a serialized batch update will be most effective if you drop the bitmap indexes before the batch and rebuild them afterwards.

The whole article is good and ends with some key facts to remember about bitmap indexes:

If a B*tree index is not an efficient mechanism for accessing data, it is unlikely to become more efficient simply because you convert it to a bitmap index.

Bitmap indexes can usually be built quickly, and tend to be surprisingly small.

The size of the bitmap index varies dramatically with the distribution of the data.

Bitmap indexes are typically useful only for queries that can use several such indexes at once.

Updates to bitmapped columns, and general insertion/deletion of data can cause serious lock contention.

Updates to bitmapped columns, and general insertion/deletion of data can degrade the quality of the indexes quite dramatically.

Best Answer

Related Solutions

Creating Indexes to optimize query

Creating Bitmap Indexes on a Materialized View

Related Question