<Quote> If you use ALTER TABLE on a MyISAM table, all non-unique indexes are created in a separate batch (as for REPAIR TABLE). This should make ALTER TABLE much faster when you have many indexes.
This feature can be activated explicitly. ALTER TABLE ... DISABLE KEYS tells MySQL to stop updating non-unique indexes for a MyISAM table. ALTER TABLE ... ENABLE KEYS then should be used to re-create missing indexes. MySQL does this with a special algorithm that is much faster than inserting keys one by one, so disabling keys before performing bulk insert operations should give a considerable speedup. Using ALTER TABLE ... DISABLE KEYS requires the INDEX privilege in addition to the privileges mentioned earlier. </Quote> Can you post your show create table tbl_name statement for these tables that involve slow queries? Do you have alot of indexes on these slow queries? If so, would using the above help? (may have been mentioned already) ALTER TABLE tbl_name DISABLE KEYS; your slow insert or update query here; ALTER TABLE tbl_name ENABLE KEYS; HTH Keith more -> On Fri, 31 Mar 2006, Chris Kantarjiev wrote: > To: [EMAIL PROTECTED], mysql@lists.mysql.com > From: Chris Kantarjiev <[EMAIL PROTECTED]> > Subject: Re: stunningly slow query > > > Are your logs and data on the same partition? That's a bad idea for > > recovering from a blown part of the disk, but we also saw that one of > > our databases would crash when there were lots of > > inserts/updates/replaces -- other databases, which had the same > > version of MySQL and operating system, had the logs and data on a > > separate partition, and they did not crash. > > It's a MyISAM table. Are there separate logs files? If so, where? > I thought it was just .frm, .MYI and .MYD. We have MYI and MYD on > separate drives. Log files usually default to the mysql data directory, eg. /var/lib/mysql/ Putting the database files on seperate drives may slow things down alot too - unless others know better. .frm is the database definition file. .MYI is the index file, and .MYD is the data file. There is one each of these files for each myisam table in the database. I may be wrong, but I would have thought it better if these are all together on the same disk and partition for each table in the database? > We're investigating a possible MERGE organization. I'll report > back if we learn anything new. > > Thanks, > chris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
