Re: Index before or after inserts?
If you are using the Load Data function then it doesn't matter. Load Data turns indexing off and then rebuilds them at the end. If you are using normal inserts then add the indexes after the import. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
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]
Re: Index before or after inserts?
On Nov 27, 2003, at 10:32 AM, Mirza wrote: 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? Insert the records first, then index. Otherwise, the indexes have to be updated with each and every insert. ___/ / __/ / / Ed Leafe Linux Love: unzip;strip;touch;finger;mount;fsck;more;yes;umount;sleep -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Index before or after inserts?
Mirza, Definitely, index after insert. Andy -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]
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 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]
Re: Index before or after inserts?
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]
