> Assuming the data set and alter table query stated before, would it
> be faster to use blocking algorithm? If so, I guess we could simply
> inform MySQL server to use blocking algorithm. Well, at least until
> real multi-index create is not implemented.
No, even in the blocking case, Falcon will build one index at a time.
If you create a table with indexes in Falcon and load it in one
big transaction, the transaction will create index segments for
each index up to the index chill limit. The chilled sections
go into the serial log. When the transaction commits, the gopher
goes to work merging segments one at a time to build the final
version of each index. That's probably the fastest way to load
If you load data into a table then create three indexes at
once, first, the server will commit the data load since the
alter table is a DDL statement. As of that moment, the data
is available, and other transactions can select, insert,
update, and delete it. At the same time, Falcon will start
processing the alter table, and as you said, it will create
one index after another, reading the table for each index.
While it is creating an index, other transactions that change
the table will create their own deferred index segments for
that index. They can't use the index to locate records.
When the gopher builds the actual index, it includes all
chilled segments - those of the transaction that's building
the index and those of other transactions changing the
So that gives pretty good concurrency - other transactions
can start using the table as soon as the data is there.
A next step would be to build the index segments for all
indexes during a single pass over the data, so as to avoid
churning the cache by reading the same table over and over.
That algorithm does the same work as the load with indexes,
plus a single read of the table.
A further step might be to find a way for the gopher to merge
segments in parallel, so the index is built from front to
back in a single pass, rather than front to back for each
segment. The code that walks indexes for navigational access
could be a model for that - it merges the primary index and
all necessary deferred index segments to produce a single
ordered output. That should be faster than either the
load with indexes or the other single pass build.
> There is probably another option, if Falcon supports it. Server has
> syntax like ALTER TABLE ... DISABLE|ENABLE KEYS. With MyISAM, DISABLE KEYS
> disables all non-unique indexes quickly (something like setting a bit in
> index file). ENABLE KEYS rebuilds all indexes, but the server doesn't
> perform data copy.
I don't know now Falcon handles that syntax. Certainly we can
drop an index very quickly to handle DISABLE. We could handle
ENABLE exactly like an on-line CREATE INDEX.