SHOW TABLES absurdly slow in Rails

For some reason, extremely simple queries, such as SHOW TABLES, can take a long time on my local machine.

# line from log/development.log 
SQL (955.1ms)  SHOW TABLES

If I execute the same query in dbconsole manually, it runs in < 0.0开发者_高级运维1s

Looking through the logs for any long running queries, aside from migrations, they're all SHOW TABLES.

  SQL (170.6ms)  SHOW TABLES
  SQL (165.7ms)  SHOW TABLES
  SQL (166.1ms)  SHOW TABLES
  SQL (176.9ms)  SHOW TABLES
  SQL (166.1ms)  SHOW TABLES
  SQL (273.7ms)  SHOW TABLES
  SQL (165.9ms)  SHOW TABLES
  SQL (145.6ms)  SHOW TABLES
  SQL (142.8ms)  SHOW TABLES
  SQL (165.8ms)  SHOW TABLES
  SQL (165.9ms)  SHOW TABLES
  SQL (166.1ms)  SHOW TABLES
  SQL (199.2ms)  SHOW TABLES
  SQL (155.0ms)  SHOW TABLES
  SQL (143.7ms)  SHOW TABLES
  SQL (143.4ms)  SHOW TABLES
  SQL (153.1ms)  SHOW TABLES
  SQL (354.5ms)  SHOW TABLES
  SQL (210.3ms)  SHOW TABLES
  SQL (1060.0ms)  SHOW TABLES
  SQL (854.7ms)  SHOW TABLES
  SQL (254.4ms)  SHOW TABLES

Running Mac OS X with Server version: 5.1.46 MySQL Community Server (GPL)

Had this problem on Rails 2 and Rails 3.

Try to switch to the new mysql2 gem. This might solve the problem.

/Carsten

You can try 'caching' the columns:

In initializers/some_file.rb do this:

User.columns
OtherModel.columns
and so on...

This will be executed when you fire the server up, so the first time you execute a query it won't be delayed by the SHOW TABLES query.

This isn't a solution, of course, there must be something wrong with your tables, besides, I don't use this in production, I use this trick when I'm debugging something in the console to avoid Rails to show all the SHOW TABLES queries.