List:General Discussion« Previous MessageNext Message »
From:Michael Widenius Date:October 18 1999 12:44am
Subject:creating indexes - slooow
View as plain text  
>>>>> "Michal" == Michal Szymanski <msz@stripped> writes:

Michal> Hi,
Michal> This is not a bug report, rather a question of a type "is that normal
Michal> that ...?" I admit being a newbie to MySQL, but really not to 
Michal> databases, programming etc.

Michal> I'm using 3.23.4 freshly compiled on Solaris 7, running on a 333MHz
Michal> Sparc Ultra 10 machine, 128 MB RAM, 450 MB swap.

Michal> I have a database table of ca. 2200000 records, 86 bytes each record,
Michal> mainly FLOATS and DOUBLES. The main MYD file is 190 MB.
Michal> Now I am creating a few indexes to this table, each one on a separate
Michal> (floating point) column, using 

Michal> ALTER TABLE smc_bvi ADD INDEX (column_name)

Michal> The first one was done in about 5 minutes. Second in 8 or so.
Michal> Third - 31, fourth - 46, fifth - 1h11m. The sixth has already taken
Michal> over 3 hours and is still running. And I want to make one more ;-(

Michal> Is that normal? What if I had 20 million records instead of 2 million?
Michal> Would it end before end of the millennium? Why every next index is
Michal> taking more and more time? In my intention all the indexes were supposed
Michal> to be "independent". Am I doing something wrong?

Michal> I've searched the manual - it says quite a bit about "performance"
Michal> versus "using indexes", but I couldn't find anything about performance
Michal> issues in *creating* indexes.

Michal> I added following options to 'mysqld' (but it did not help much, I'm
Michal> afraid): 

Michal> -O key_buffer=16M -O table_cache=128 -O sort_buffer=8M -O record_buffer=1M

Michal> I also tried to rebuild the table from scratch, putting indexes 
Michal> definitions into "create table" (before loading data), but this was also
Michal> very slow.

Michal> I understand that an MySQL index is something more complicated that a
Michal> simple list of record numbers written in column-sorted order. Still,
Michal> numerical sorting of 2 million double values on that machine takes 25
Michal> seconds. 

Michal> Any hints, explanations would be appreciated.

Hi!

Take a look in the MySQL manual about the hints with using isamchk and 
LOAD DATA INFILE ;  These also applies here.  (isamchk uses sorting to 
create indexes)

Anyway, it's faster to add all indexes at once.

The reason it's 'relative' slow to insert indexes is that index
inserting is 'relative' ;  It should be about as fast to insert a row
at any time.  The B+ trees does a good job at this, but when one loads
data the first time it would be nice to have quick index create
option.

We have very high on our TODO to move myisamchk into the MySQL
server.  This will at the same enable us to make fast index
creation...

Regards,
Monty
Thread
creating indexes - slooowMichal Szymanski15 Oct
  • creating indexes - slooowMichael Widenius18 Oct