# MySQL query optimization

**URL:** <https://www.ruby-forum.com/t/mysql-query-optimization/76218>\
**Category:** Rails\
**Created:** [November 16, 2006, 8:33pm UTC](https://www.ruby-forum.com/t/mysql-query-optimization/76218 "2006-11-16T20:33:16Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![casper\_the\_ghost](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/58956e/32.png) [@casper\_the\_ghost](https://www.ruby-forum.com/u/casper_the_ghost)\
**Post date:** [November 16, 2006, 8:33pm UTC](https://www.ruby-forum.com/t/mysql-query-optimization/76218/1 "2006-11-16T20:33:16Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![casper\_the\_ghost](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/58956e/32.png) [@casper\_the\_ghost](https://www.ruby-forum.com/u/casper_the_ghost)\
**Post date:** [November 16, 2006, 8:43pm UTC](https://www.ruby-forum.com/t/mysql-query-optimization/76218/2 "2006-11-16T20:43:19Z")

</div>

[http://www.databasejournal.com/features/mysql/article.php/3110171](http://www.databasejournal.com/features/mysql/article.php/3110171)

---

<div class="post-metadata">

**Author:** ![casper\_the\_ghost](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/58956e/32.png) [@casper\_the\_ghost](https://www.ruby-forum.com/u/casper_the_ghost)\
**Post date:** [November 16, 2006, 8:51pm UTC](https://www.ruby-forum.com/t/mysql-query-optimization/76218/3 "2006-11-16T20:51:23Z")

</div>

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](http://www.databasejournal.com/features/mysql/article.php/1382791)

Best,  
Shane

---

<div class="post-metadata">

**Author:** ![casper\_the\_ghost](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/58956e/32.png) [@casper\_the\_ghost](https://www.ruby-forum.com/u/casper_the_ghost)\
**Post date:** [November 16, 2006, 9:21pm UTC](https://www.ruby-forum.com/t/mysql-query-optimization/76218/4 "2006-11-16T21:21:33Z")

</div>

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](http://www.ZONator.com)