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!

Hi, I have a file with a million lines in the following format. field1=val1 field2="val2" field3="" It is basically a list of key/values pairs separated by a space. Where a value is NOT encased in quotes, it will have only alphanumerics or a dot or a minus sign. Where it IS encased in...

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!

Hi, I have 2 tables. One table is called "data". It's defined as follows date DATE NOT NULL total int NOT NULL It is indexed by date and has, say, 300K records per date, and a total of 45M records with a range of dates from, say ,20090101 to 20110401 second table is called "rate" and is...

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

Rudy, My bad, I actually did mean to post the explain and forgot. Unfortunately, I cannot see the issue from it :/ but here it is mysql> explain SELECT g.date,g.account_id,sum(pnl) from globalPnL g where date between 20110401 and 20110419 group by date,account_id...

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?

Here are the details. 1) Here are 2 tables mysql> show create table globalPnL; | globalPnL | CREATE TABLE `globalPnL` ( `date` date NOT NULL, `symbol` varchar(32) NOT NULL, `account_id` int(16) NOT NULL, `currency` varchar(8) NOT NULL default 'usd', `pnl` float(13,4) NOT NULL...

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