Greetings,
I generate this report of all the holdings by symbol, summing up over
accounts. It is taking much too long. I was wondering if I can add
an index on something to make it more efficient. The first query
gets the most recent date, the next query sums it up by symbol.
Thank you for you help,
Joe.
# mdate = $m.query("select max(date) from positions where symbol = '#
{sym}'
# and date >= '#{last_date}' ").fetch_row.at(0)
# this query was taking too long to process. It has been replace
with the
# following:
line = $m.query("select * from positions where symbol = '#
{sym}'
order by date desc limit 1").fetch_row
next if line.nil?
mdate = line.at(4) # fourth position is the date
next if last_date > mdate
total = $m.query("select sum(value) from positions where
symbol = '#{sym}'
and date = '#{mdate}' ").fetch_row.at(0).to_f
mysql> describe positions;
+---------+-------------+------+-----+------------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+------------+-------+
| account | varchar(12) | | PRI | | |
| symbol | varchar(12) | | PRI | | |
| shares | varchar(12) | YES | | NULL | |
| value | varchar(20) | YES | | NULL | |
| date | date | | PRI | 0000-00-00 | |
+---------+-------------+------+-----+------------+-------+
5 rows in set (0.00 sec)
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
