Search results for query: *

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

prex1, Thank you!! is it worth it? When a vital production script on a deadline takes 1.5 hours to run instead of 3 hour, hell yea it's worth it!

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

Wow, this is hot! thanks, Rudy!

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

Could the fact that I am using "between" for date range be the culprit?

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 =...

OK...but the problem is I want the first (or min or max or whatever - anything!) of every distinct f1. I am not sure how to get that...

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