One thing you could try is limiting your index length ALTER TABLE `sometable` ADD INDEX(`somecolumn`(10))
This will only index off of the first 10 characters of your data. Depending on the type of data you store, this may improve your performance. ----- Original Message ----- From: "Mikko Noromaa" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Monday, June 03, 2002 9:11 AM Subject: INSERT performance when indexes are used > Hi, > > I have a table with about 5 million records, and an index on that > table's VARCHAR(255) field, as well as a couple of integer indexes. The > VARCHAR-index is essential to my application as I frequently need to > search through the whole table by using the VARCHAR field. > > When I do INSERTs into this table, they sometimes take a considerably > long time to complete. For example, inserting 1000 rows sometimes takes > 700 ms, sometimes 70000 ms. I assume this is because the indexes need to > be modified. Is it possible to leave extra space in the index tree > leaves so that individual INSERTs could work without modifications to > the whole index tree? > > I know that I'll have about 10000-20000 INSERTs per day, and I'd like > each of them to work as fast as possible (preferably less than 1 ms). > Since the table grows anyway, I don't mind the index files containing > some unused space. The table is a MyISAM table, with a 1 GB MYD-file and > a 400 MB MYI-file. > > I know there are workarounds to this problem (doing inserts in a single > batch instead of several, loading data from a text file, etc.), but for > now I'd just like to know how well the indexes can be customized. > > I am running MySQL 3.23.49 on Windows NT with MyISAM tables. > > -- > > Mikko Noromaa (mikkon@nm-sol) - tel. +358 40 7348034 > Noromaa Solutions - see http://www.nm-sol.com/ > > > > --------------------------------------------------------------------- > Before posting, please check: > http://www.mysql.com/manual.php (the manual) > http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
