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)?
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.
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.
