I'd start by looking at your schema.. Quite a lot of use of
varchars... I'd suggest using chars (takes more space but processes
faster).. Or even better, normalize the data so that you have a table
for symbols that is linked to this table via an integer based foreign
key.
Also it seems wierd for 'shares' and 'value' to be varchar? I'd think
them to be integer and float types..
Also using date as the PK seems odd..
A lot of times queries perform poorly because of poor database/table design.
On 5/31/06, Joseph Alotta <[EMAIL PROTECTED]> wrote:
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]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
