maria-discuss team mailing list archive

[Sergei Golubchik <serg@xxxxxxxxxxx>]

Hi, Sergey!

On Feb 12, Sergey Vojtovich wrote:
> > This is used when you insert data into the empty table. Or when you add
> > an index to the existing table. Or when you enable indexes. That is:
> > 
> >  1. LOAD DATA INFILE 'everything, not in chunks'
> >     (a convenient way would be to load from a pipe, and cat all your
> >     chunks into it).
> >  2. ALTER TABLE ... ADD KEY
> >  3. ALTER TABLE DISABLE KEYS. insert the data. ALTER TABLE ENABLE KEYS
> > 
> > But unfortunately, all this only works for non-unique keys, when MariaDB
> > doesn't need to expect a unique constraint error and won't need to
> > delete rows when creating indexes.  For unique indexes values will be
> > inserted into a b-tree one by one.
>
> Indeed, it goes through key cache. Just curious why is that needed for
> p.2 - it should never have to delete rows. AFAIR it should just drop
> temporary table in case of failure. I guess it was fixed this way in 10.0.
> 
> And p.1 can just truncate data file...

For repair-by-sort, ALTER TABLE needs first insert all rows, and only
then rebuild all indexes.

With UNIQUE indexes a conflict is possible, it will need to abort the
operation. Old assumption was that it's better to detect a conflict as
soon as possible - when rows are being inserted - and abort sooner. As
compared to copying everything, and only then during repair-by-sort
noticing a conflict. The second approach would end up doing much more
work before the operation is aborted.

There is some logic in that reasoning. But still, when UNIQUE index is
added, conflicts are typically rare. And even if we'd need to abort the
operation, doing more work and repair-by-sort is usually faster than
doing less work. Furthermore - what's even more important - most often
ALTER TABLE does not add a unique index, but performs some unrelated
operation on the table. In this case one cannot expect any conflicts at
all in the existing unique indexes.

So, in 10.0 we've changed ALTER TABLE to use repair-by-sort also for
unique indexes.

Regards,
Sergei