# Performance of Rails and mySQL **URL:** \ **Category:** Rails\ **Created:** [March 14, 2007, 2:28am UTC](https://www.ruby-forum.com/t/performance-of-rails-and-mysql/88754 "2007-03-14T02:28:16Z")\ **Posts on this page:** 3\ **Page:** 1
**Author:** ![asa](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/a/dfb087/32.png) [@asa](https://www.ruby-forum.com/u/asa)\ **Post date:** [March 14, 2007, 2:28am UTC](https://www.ruby-forum.com/t/performance-of-rails-and-mysql/88754/1 "2007-03-14T02:28:16Z")
I am trying to figure out how best to optimize a rails view that I am serving from DreamHost. Development Log: Controler Load (18.165430)… SQL… Completed in 22.34315 (0 reqs/sec) | Rendering: 2.35181 (10%) | DB: 18.17923 (81%) | 200 OK [[http://development.site.com/controller/action/](http://development.site.com/controller/action/) id] It appears that the performance issue is the SQL statement which takes 81% of the elapsed time. Elasped time within mysql via SSH: 146 rows in set (9.50 sec) Elasped time within phpMyAdmin sql tab: 146 total, Query took -0.9953 sec Why is phpMyAdmin so much faster? I did an explain on the SQL within mysql: ±—±------------±------±------±-----------------±------------- ±--------+ --------------------------±-----±--------------------------------+ | id | select\_type | table | type | possible\_keys | key | key\_len | ref | rows | Extra | ±—±------------±------±------±-----------------±------------- ±--------+ --------------------------±-----±--------------------------------+ | 1 | PRIMARY | po | const | PRIMARY,username | PRIMARY | 4 | const | 1 | Using temporary; Using filesort | | 1 | PRIMARY | sk | ref | portfolio\_id | portfolio\_id | 4 | const | 102 | Using where | | 1 | PRIMARY | sp | ref | symbol | symbol | 22 | ts\_development.sk.symbol | 1 | | | 1 | PRIMARY | pe | ref | symbol,quotedate | symbol | 22 | ts\_development.sk.symbol | 17 | | | 2 | SUBQUERY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Select tables optimized away | ±—±------------±------±------±-----------------±------------- ±--------+ --------------------------±-----±--------------------------------+ 5 rows in set (0.02 sec) Any ideas on how to optimize this custom SQL which contains three left joins and a sub-select query, that is run via Controller.find\_by\_sql(@sql)? Thanks for any help, Asa ---
**Author:** ![asa](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/a/dfb087/32.png) [@asa](https://www.ruby-forum.com/u/asa)\ **Post date:** [March 14, 2007, 4:49am UTC](https://www.ruby-forum.com/t/performance-of-rails-and-mysql/88754/2 "2007-03-14T04:49:30Z")
Asa wrote: > It appears that the performance issue is the SQL statement which takes > I did an explain on the SQL within mysql: > 4 | > | 2 | SUBQUERY | NULL | NULL | NULL | NULL | > NULL | > NULL | NULL | Select tables optimized away | > ±—±------------±------±------±-----------------±------------- > ±--------+ > --------------------------±-----±--------------------------------+ > 5 rows in set (0.02 sec) > > Any ideas on how to optimize this custom SQL which contains three left > joins and a sub-select query, that is run via > Controller.find\_by\_sql(@sql)? Without knowing the query(s) that Rails is executing it’s impossible to say. There could any number of other queries that are being executed in the view besides the one you are doing with find\_by\_sql since you haven’t shown us the view or controller so that one might not even be the one that’s taking most of the time. – Michael W. ---
**Author:** ![asa](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/a/dfb087/32.png) [@asa](https://www.ruby-forum.com/u/asa)\ **Post date:** [March 14, 2007, 1:25pm UTC](https://www.ruby-forum.com/t/performance-of-rails-and-mysql/88754/3 "2007-03-14T13:25:42Z")
You might want to check out the query\_stats and [less so] query\_trace plugins. They’ll give you a lot more info about the sql used than you’re providing right now. 🙂 RSL