WL#1333: Speed up ALTER TABLE (part 3: Rebuild unique indexes by sorting in ALTER TABLE)

Affects: Server-7.1 — Status: On-Hold — Priority: Medium

Currently in MyISAM tables non-unique indexes are rebuilt by sorting on ALTER
TABLE (OPTIMIZE TABLE etc) which is fast, unlike it UNIQUE  (and PRIMARY) keys
are rebuilt by key_cache which is very slow on large table sizes especially if
keys are comming in random order  - the IO becomes random and so one is able to
insert just some hundred index values per second on commodity hardware.

We shall be able to use rebuilding by sort for unique indexes as well (using
modified filesort which alerts on dublicates) at least for the cases when we do
not need to find error as early as possible. 

One way to do it is to use UNIQUE_CHECKS variable (now it is for InnoDB only)

Current Tags

fast alter table  crystalfeel 

You must be logged in to tag this worklog

On Aug 17, 2009 @ 14:21, Markweee said:

I use the abbreviation FK when discussing the child / referencing table (the table with the foreign key), or its indexes and columns. I use the abbreviation PK when discussing the parent / referenced table (which is often a table with a primary key), or its indexes and columns.

marketing diploma AND Bachelor degrees AND bachelor degree business

On Aug 17, 2009 @ 14:21, Markweee said:

- make foreign-key support possible for any storage engine (which could handle it) via a combination of API calls and server handling.

Information Technology degree AND Online degrees

On Feb 2, 2009 @ 11:19, Pat Thomas said:

This is one that I need to watch for progress. Look forward to reading an update on this topic.

Pat.

On Apr 2, 2008 @ 00:07, P Eger said:

Also, "alter table add index" rewrites the entire table, including all other indexes on the table. Can this be avoided as part of this work?

Votes

  • Rated 5.00 out of 5
Rated 5.00 out of 5 with 8 votes cast.
You must be logged in to vote.

Watches

11 members are watching this worklog
You must be logged in to track this worklog.

Provide Feedback

Please note:
HTML will be purified, but we allow for a number of HTML tags so that you have the flexibility to decorate your comment text to some extent. The comments allow the following HTML tags:

strong, b, em, blockquote, a, code, pre

To put code into your comment, simply encapsulate your code with
[code language="XXX"][/code], where XXX is any common language, for instance "PHP", "SQL", "C", etc.

You must be logged in to comment