MySQL query optimization

Rails
1

We have created a Ruby on Rails website which is based on searching a
table of 70,000+ object. Each object searched then returns a list of
id’s in another table, of about 30,000. The id’s are listed for each
object in the first table.

The problem is, when we search for object which are very frequently
met, the query runs extremely slowly, and after looking at the query
log, seems to be made up of a number of OUTER JOINS.

If anybody has any idea how to optimize this kind of query, feedback
would be greatly appreciated.

2

http://www.databasejournal.com/features/mysql/article.php/3110171

3

I ask enough questions here. I should answer some too!
Another good link and in the same journal is this:
http://www.databasejournal.com/features/mysql/article.php/1382791

Best,
Shane

4

70000 is a “small” table, with proper indexes should work fine.

or maybe normalization is not always a good idea…

anyway, try to query by sql first.

ZON
http://www.ZONator.com