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 > To: [EMAIL PROTECTED] > 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] > >
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
