Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Rhinorhino on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Search results for query: *

  1. azzazzello

    Subquery efficiency when used in IN

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

    regular expression efficiency

    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!
  3. azzazzello

    regular expression efficiency

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

    Dynamic maintenance of a summary table from a detail table

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

    which is more efficient....

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

    Dynamic maintenance of a summary table from a detail table

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

    Joining tables on sequential values

    Wow, this is hot! thanks, Rudy!
  8. azzazzello

    Joining tables on sequential values

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

    Getting latest tagname when aggregating data

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

    Getting latest tagname when aggregating data

    I have the following query: mysql> desc tradesTable; +--------------+-------------+------+-----+-------------------+-------+ | Field | Type | Null | Key | Default | Extra | +--------------+-------------+------+-----+-------------------+-------+ | date | date...
  11. azzazzello

    Making a join more efficient

    *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/
  12. azzazzello

    Making a join more efficient

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

    Making a join more efficient

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

    Making a join more efficient

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

    Making a join more efficient

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

    Making a join more efficient

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

    MySQL from remote server: Catastrophic Error

    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?
  18. azzazzello

    Making a join more efficient

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

    Selecting one row per field

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

    Query help

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

Part and Inventory Search

Back
Top