Hi, Create the indexes right away and then use
ALTER TABLE table DISABLE KEYS; Load your data and then ALTER TABLE table ENABLE KEYS; This will not make a tmp copy of the data file, but will simply start rebuilding the index. However, DISABLE KEYS doesn't disable unique indexes, so these still have to be updated as opposed to adding them afterwards. This is probably good though for integrity. Using LOCK TABLES around multi-row INSERTs will make index updating much faster than single-row non-locked INSERTs. And a large enough key_buffer will make the indexes be flushed less often. For ENABLE KEYS, I think myisam_sort_buffer_size is the important variable. Also note: DISABLE/ENABLE KEYS doesn't work in 3.23, but you can do the same thing using myisamchk. Hope that helps. Matt ----- Original Message ----- From: "mos" Sent: Thursday, November 27, 2003 3:44 PM Subject: RE: Index before or after inserts? > At 03:19 PM 11/27/2003, you wrote: > >Mirza, > > > >Definitely, index after insert. > > > >Andy > > Maybe not. <g> I know this is the common sense approach that works with > many databases, but I'm not sure it is faster with MySQL. > > MySQL Manual on "Alter Table " > >>Note that if you use any other option to ALTER TABLE than RENAME, MySQL > will always create a temporary table, even if the data wouldn't strictly > need to be copied (like when you change the name of a column). We plan to > fix this in the future, but as one doesn't normally do ALTER TABLE that > often this isn't that high on our TODO. For MyISAM tables, you can speed up > the index recreation part (which is the slowest part of the recreation > process) by setting the myisam_sort_buffer_size variable to a high value. << > > So if the index is added later (after the data is inserted), a new > temporary table is created and the data is reloaded. MySQL probably does it > this way to ensure the table isn't destroyed if something happens in the > middle of the table restructure. > > Now if you really, really want to add the indexes later, make sure you add > all the indexes in *one* "alter table" command so the data is reloaded only > once. Otherwise it will get loaded for each "alter table" > > Mike > > > > > -----Original Message----- > > > From: Mirza [mailto:[EMAIL PROTECTED] > > > Sent: 27 November 2003 15:33 > > > Subject: Index before or after inserts? > > > > > > > > > I need to insert hundreds of milions of records to a table and make > > > several indicies on it. Now, is it faster to make tables with indicies > > > and then fill tables or fill tables first, then make indicies? Any > > > experiancies? > > > > > > regards, > > > > > > mirza -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
