Thank you for your help. I will try using chars instead of
varchars. That should be fairly
easy to try. I already do have a table for symbols, though the key
is varchar not integer.
I was using shares and value as varchar because there was some weird
rounding happening when I
used decimal, or floats. It seemed easier to keep them as
character. Also the date is part
of the key since for different days there is different values as the
stock market changes.
I will try char though and see if that makes it faster.
Thank you.
From: DreamWerx <[EMAIL PROTECTED]>
Date: May 31, 2006 12:25:50 PM CDT
To: "Joseph Alotta" <[EMAIL PROTECTED]>
Cc: mysql@lists.mysql.com
Subject: Re: please help optimize this query
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]
