InnoDB Index Drops

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

InnoDB Index Drops

Post 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?
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post 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?
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Post by Benjamin »

No I haven't. I did read something about InnoDB having to rewrite every row when you drop indexes though.
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Post 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.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

Maybe it'd be faster to dump the table, drop the table, tweak the table definition and inject it back in..?
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Post 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.
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Post 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.
Post Reply