"SHOW INDEX" very slow on large database

[Chris Taggart]

We're using MySQL 5.0.51 on Debian. I've got a large database (over 80 million rows on one table, 30+ million on another, 20 million on another etc, each with an integer primary key, and most tables are fairly wide too), and the server is being worked fairly hard.

The framework we're using (Ruby on Rails) has made a minor tweak, which is causing real problems, and I'm trying to see whether it's something that affects just us, or whether it's intrinsic in the type of query it's doing.

On starting a new Rails process, the framework runs "SHOW INDEX FROM `foo` WHERE Key_name = 'PRIMARY'" for each of the tables. On our server that's taking up to 20 seconds for the bigger tables. That adds up to a lot of seconds.

Is this expected behaviour, or will it only happen in certain limited circumstances, and are there any solutions (one might be to use "SHOW CREATE TABLE foo" to get the same information).

Thanks

Chris