Hello ,
I must create a temp table with 5 or more million records. I need it for creating some reports, so I need to create indexes too. My question is: When total time for work will be smaller? : 1-st case: I create table and create indexes immediately before filling data. 2-nd case: I create table, filling data and then create indexes?
And the winner is #1, well maybe. :-)
The reason is when you use Alter Table to add an index, it creates a empty temporary table with the new table structure and indexes, MySQL then copies the old data to the temporary table (with the new indexes) so your hard disk needs at least twice the disk space as the original table. If that succeeds, MySQL drops the old table and renames the temporary table to the proper table name. So #2 must do an additional unload and reload of the data. Of course if you use separate "Alter Table Add Index" statements, it will do this each time. If you add all the indexes in one Alter Table statement the data is only unloaded and reloaded once.
5 million rows is peanuts.<g> I have tables with up to 100 million rows and I never build indexes later after the data is in there if I don't have to. Otheriwse I'd need another 6gb free for the temporary table.
If you are loading a lot of data at one time, I definitely recommend using "Load Data". It is much faster than Insert. If you can't use Load Data then #2 could be faster if you use only 1 Alter Table statement to build all the indexes.
Mike
-- Best regards, Krasimir_Slaveykov mailto: [EMAIL PROTECTED] [EMAIL PROTECTED]
|-------------------------------------------------| |///// **** ***** ***** *** **** ***** ** /| |//// *** *** *** *** *** *** ** *** **** //| |/// *** *** ***** ***** *** *** ***** *** ///| |// *** *** *** *** *** *** ** *** *** ////| |/ **** *** *** *** **** ***** *** /////| |--------------- www.office1.bg ------------------|
-- 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]
