Hi,
Suppose I have the following query
SELECT sum(some_value) from some_table s
where s.date in (date1,date2,date3)
Suppose I want to make it contingent on some other table and basically do the following
SELECT sum(some_value) from some_table s
where s.date in ( select distinct(date) from...
...the following to parse them, but something tells me there is a faster way of doing it. Any ideas?
my %vals;
while ($line =~ /([^=\"]+)=((\"(.*?)\")|([\-\w\.]*?))\s+/g)
{
($key,$val) = ($1,$2);
$val =~ s/"//g if $val;
$vals{$key} = defined($val) ? $val : "";
}
The reason I am "over-engineering" in this way is because I have 50M+ records and growing and because Mysql has a documented bug in using composite indexes when the first is a range (see my previous thread). The problem that I am having is that because of this, a fairly standard query that...
The amount of work the CPU does is the same (until you stop the loop), except that it is spread over a much greater amount of time in second case. So if you stop it after a minute in both cases, then your CPU would have done an awful lot more in first case than second, because it would have...
Hi,
Suppose I have 2 tables.
One is called "detail"
date DATE
account_id int
stock varchar(10)
trades int
pnl int
PRIMARY KEY (date,account_id,stock)
This table is useful for research, however I expect that 95% of the queries will be grouping by date and account_id. Since there are...
...This basically defines a range of dates for which a rate is valid
I want to return every row from "data" such I get
data.date,total,total*rate where rate is from a rate.date that is CLOSEST AND LESS THAN that of the data.date
I hope this can be done fairly efficiently despite the fact...
Rudy,
Well, it works, but because the table is huge, it makes the query about 6 times as slow. I am doing the following for data:
sum(case when date_range.date = 20110419 then date_range.trades else 0 end) as daytrades
, sum(case when date_range.date >= 20110401 and date_range.date <=...
I have the following query:
mysql> desc tradesTable;
+--------------+-------------+------+-----+-------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-------------+------+-----+-------------------+-------+
| date | date...
*sigh* it's a MySQL bug.
It's documented and analyzed here. It has to do with combining ranges for index purposes, and I do not think it matters if it's a date or not :/
http://www.mysqlperformanceblog.com/2008/08/01/how-adding-another-table-to-join-can-improve-performance/
BTW, to show what I mean, here is what I am doing. Now, mysql IS using the right indexis 5 times faster than the best one I had so far. I would REALLY appreciate it if someone told me how to store DATE better, make my index more efficient, or construct a better query. It is not an option to...
Hmm. It is in fact date between x and y that is the culprit. When I specify dates explicitly (date in (x,x+1,x+2,...,y)), the right index DOES get used and the query is much faster. 5 times faster.
I am investigating why that is, and whether changing my date column, which is ALWAYS an...
Rudy,
This is baffling me as well. Here is something even more baffling. When I specify an account id to use EXPLICITLY, it still does not use the date_account_symbol index!!
mysql> explain SELECT SQL_NO_CACHE g.date,g.account_id,sum(pnl) from globalPnL g where `date` between '20110401'...
...1 row in set (0.01 sec)
mysql> SELECT SQL_NO_CACHE g.date,g.account_id,sum(pnl * (case when c.rate is not NULL then c.rate else 1 end)) as convertedpnl from globalPnL g
LEFT JOIN currency_rates c on
g.date = c.rate_date and
g.currency =...
Hmm....you are connecting to a MySQL 5 server with a mysql 3 client? I dunno...sounds fishy. There may have been changes in how authentication is done. How about upgrading the ODBC driver?
...the same query without joining the currency table. Can someone suggest what may be done to alleviate this?
SELECT g.date,g.account_id,sum(pnl * (case when c.rate is not NULL then c.rate else 1 end)) as convertedpnl from globalPnL g
LEFT JOIN currency_rates c on
g.date =...
Suppose I have a table consisting of 10 fields.
f1,f2,...,f10
f1 is not the primary key, and hence has repetitions
suppose I have
the following set of data
AAA,1,2,...
BBB,3,4,....
AAA,5,9,...
CCC,4,1,...
BBB,3,7,...
I need to return any ENTIRE row for a distinct value of f1.
So ideally...
This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
By continuing to use this site, you are consenting to our use of cookies.