Page 1 of 1

InnoDB Index Drops

Posted: Thu Aug 31, 2006 6:24 am
by Benjamin
What is the fastest way to drop indexes in an InnoDB database table. It's taking hours and hours to drop indexes and fields. Do I need to drop the primary key?

Posted: Thu Aug 31, 2006 7:15 am
by volka
viewtopic.php?t=54529
astions wrote:Umm, LOL, any way to speed this up?
mysql> select count(*) as records from text;
+---------+
| records |
+---------+
| 1679000 |
+---------+
1 row in set (1 hour 27 min 59.65 sec)
I'm under the impression your mysql server is a banana problem.
Did you try the same things on a completely different machine just for comparison?

Posted: Thu Aug 31, 2006 12:51 pm
by Benjamin
No I haven't. I did read something about InnoDB having to rewrite every row when you drop indexes though.

Posted: Thu Aug 31, 2006 10:08 pm
by Benjamin
Well I must say that while InnoDB can be incredibly fast when indexed correctly, degragmenting, indexing, dropping indexes and the like are excrutiatingly slow.

Posted: Thu Aug 31, 2006 10:13 pm
by feyd
Maybe it'd be faster to dump the table, drop the table, tweak the table definition and inject it back in..?

Posted: Thu Aug 31, 2006 10:18 pm
by Benjamin
Yeah, now that I have been working with one for the last few days, I believe that would be the best way to go about it. I'm making a backup copy right now, and once that is done, I'm going to attempt to convert it to MyISAM. If that fails I'll just write a small script to copy the data into a new database.

Posted: Fri Sep 01, 2006 10:23 pm
by Benjamin
Ok well with InnoDB, and the same probably applies to MyISAM, watch your CPU usage. If the server is tearing up the hard drive, but the CPU usage is only at 3%, something isn't configured properly. Try using one of the my.cnf files and tweaking it to your needs. If you get the CPU usage up to 90% on a large table operation, and it barely touches the hard drive, things will be MUCH MUCH faster.

Besides that, I had to add 2gb of ram to it.

Also, don't change innodb_log_file_size after the log has been generated. That can really mess things up because mysql won't want to start. I figured deleting the log would fix it, which it did, but that corrupted the database so I had to re-import it again.

Picky stuff.