CODEWITHSUNDEEP
mysqlsqlsql-serverindexingrebuild
Aki
Aki

Reputation: 626

Rebuild Index on table with 128 million rows

I have a database table with over 128 million rows.

The issue I have to deal with is the indexes, the database performs very bad over time, I put it down partly to the indexes getting fragmented. One of the current indexes on the large table is around 50% Total fragmentation.

Reorganizing did about 1% in 1 hour so it would take way too long.

On such a large table, a re-index can take as much as 5 hours if not more, and I haven't found a real way of monitoring progress. What would be the best and quickest way to re-build an index on such a large table? Should I set the database as "OFFLINE" ?

The database also runs a very large and busy website, so I've scheduled a maximum of 6 hour downtime to get this done but need the quickest and best way possible to do this.

I also need to update all other indexes on the database, but this table is the hardest one.

Upvotes: 3

Views: 3062

Answers (1)

mrjoltcola
mrjoltcola

Reputation: 20842

If you havent measured the following 2 things on this particular index, you probably don't yet have a reason to rebuild.

  1. How much (if any) your queries actually improve immediately after the rebuild.
  2. How long the improvement lasts (ie. How long your index takes to return to a steady state of 50% fragmentation after a rebuild.

B-Tree indexes are designed to have fragmentation / bloat / free-space. Indexes often quickly return to their steady state of fragmentation. They usually perform decently in this state tend to want to return to steady state.

Upvotes: 3

Related Questions