Some sections of the manual seem to indicate that it's better to create indexes after data population, rather than before. See section 7.2.14, "Speed of INSERT Statements". The general procedure there is to load the data using LOAD DATA INFILE, and then use myisamchk. "This creates the index tree in memory before writing it to disk, which is much faster because it avoids lots of disk seeks. The resulting index tree is also perfectly balanced." (ibid)
On 8/4/05, Michael Stassen <[EMAIL PROTECTED]> wrote: > Sebastian wrote: > > > i forgot to add another question, > > > > is it ok to add index after the tables and data are already built, or is > > it better to create the index before data gets inserted? > > It's probably better to create the indexes up front (assuming you know in > advance which ones will be needed), but I think that's a moot point if your > table already exists and is full of data. There's no sense starting from > scratch when you can simply add the index with > > ALTER TABLE yourtablename ADD INDEX name (item, type); > > Be aware, however, that mysql alters a table by making a new, temporary table > to match the new definition (old def + alterations), fills it with data from > the original table, then replaces the old with the new. The time to do this > grows with the size of your table and number of indexes. While it is in > progress, statements which would change the data (e.g. INSERT, UPDATE) are > locked out. See the manual for details > <http://dev.mysql.com/doc/mysql/en/alter-table.html>. > > Michael > > > -- > 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]
