CODEWITHSUNDEEP
mysqlperformanceindexing
Tom
Tom

Reputation: 175

Mysql: Unique Index = Performance Characteristics for large datasets?

what is the performance characteristic for Unique Indexes in Mysql and Indexes in general in MySQl (like the Primary Key Index):

Given i will insert or update a record in my databse: Will the speed of updating the record (=building/updating the indexes) be different if the table has 10 Thousand records compared to 100 Million records. Or said differently, does the Index buildingtime after changing one row depend on the total indexsize?

Does this also apply for any other indexes in Mysql like the Primary Key index?

Thank you very much Tom

Upvotes: 1

Views: 1266

Answers (3)

Marcus Adams
Marcus Adams

Reputation: 53830

Note that if new primary key values are always larger than the previous (i.e. autoincrement integer field), your index will not need to be rebuilt.

Upvotes: 0

Alex Martelli
Alex Martelli

Reputation: 881595

A typical MySQL implementation of an index is as a set of sorted values (not sure if any storage engine uses different strategies, but I believe this holds for the popular ones) -- therefore, updating the index inevitably takes longer as it grows. However, the slow-down need not be all that bad -- locating a key in a sorted index of N keys is O(log N), and it's possible (though not trivial) to make the update O(1) (at least in the amortized sense) after the finding. So, if you square the number of records as in your example, and you pick a storage engine with highly optimized implementation, you could reasonably hope for the index update to take only twice as long on the big table as it did on the small table.

Upvotes: 1

Bill Karwin
Bill Karwin

Reputation: 562280

Most indexes in MySQL are really the same internally -- they're B-tree data structures. As such, updating a B-tree index is an O(log n) operation. So it does cost more as the number of entries in the index increases, but not badly.

In general, the benefit you get from an index far outweighs the cost of updating it.

Upvotes: 3

Related Questions