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/
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