bulk updates/inserts and triggers
Hello, I am facing a problem related with triggers and bulk updates/inserts. I have two tables one is having 'transactions' and another is 'documents'. Each record in transactions table relates with a document by DocId. Foreign keys are created. I have activated triggers for transactions table for after insert, afters update, before delete. trigger makes a sum of amount in all transactions for a document referenced by docId in that perticular transaction and stores it in another temporary table and then updates the amounts in documents table. When I will go for inserting 10K records at a time, the insert becomes too slow. Why? Is there any solution to this? I am using MySQL 5.0.45 on Redhat Ent. Linux 5 - 64bit, 4 GB RAM, Xeon procesors and MySQL Connector ODBC 5.1 Beta, MS Acess 2003 on WindowsXp SP2. I also tried to inster this records directly by passing queries to MySQL, but still it is slow! Please help. Thanks CPK the scripts are as follows- -- -- Table structure for cb_canebills -- CREATE TABLE `cb_canebills` ( `ID` int(11) NOT NULL auto_increment, `AssociateSCPId` int(11) default NULL, `PeriodNo` int(11) default NULL, `SlipCount` int(11) default NULL, `TotalWeight` decimal(9,3) default NULL, `NormalWeight` decimal(9,3) default NULL, `JalitWeight` decimal(9,3) default NULL, `NormalAmount` decimal(15,2) default NULL, `JalitAmount` decimal(15,2) default NULL, `NormalRate` decimal(15,2) default NULL, `JalitRate` decimal(15,2) default NULL, `PayRate` decimal(15,2) default NULL, `TotalAmount` decimal(15,2) default '0.00', `HAmount` decimal(15,2) default NULL, `TAmount` decimal(15,2) default NULL, `HComissionAmount` decimal(15,2) default NULL, `TComissionAmount` decimal(15,2) default NULL, `TotalAgainstAmount` decimal(15,2) default '0.00', `NetPayAmount` decimal(15,2) default '0.00', `BankID` int(11) default '0', `BankAccNo` decimal(20,4) default NULL, `CaneBillNo` varchar(20) collate utf8_unicode_ci default NULL, `CaneBillDate` date default NULL, `AssociateType` int(11) default NULL, `CrushSeason` int(11) default NULL, `ChequeNo` varchar(10) collate utf8_unicode_ci default NULL, `ChequeDate` date default NULL, `ChequeAmount` decimal(15,2) default NULL, `BankOrCashAmount` decimal(15,2) default NULL, `InstallmentNo` int(11) default NULL, `tmpTS` timestamp NULL default '1999-11-11 11:11:11', `CreatedBy` int(11) default NULL, `CreatedTimeStamp` datetime default NULL, `LastModifiedBy` int(11) default NULL, `LastModifiedTimeStamp` datetime default NULL, `Locked` tinyint(4) default NULL, `CaneBillRemark` varchar(300) collate utf8_unicode_ci default NULL, `CoBranch` int(11) default NULL, `CoYear` int(11) default NULL, `CaneBillStatus` int(11) default NULL, `AccVoucherCreated` tinyint(4) default NULL, `Approved` tinyint(4) default NULL, `ApprovedBy` int(11) default NULL, `IsTemplate` tinyint(4) default NULL, `ReportH_RCS` int(11) default NULL, `ReportF_RCS` int(11) default NULL, `CaneBillCurrency` int(11) default NULL, `CaneBillExchangeRate` decimal(15,2) default NULL, `LastAccDate` date default NULL, `Billed` tinyint(4) default NULL, `tmpSelect` tinyint(4) default '0', `DocType` int(11) default NULL, `BillFromDate` date default NULL, `BillToDate` date default NULL, `Partial` tinyint(4) default NULL, `IsTemp` tinyint(4) default NULL COMMENT 'Temprory bill or not', `PaidThroughBankAccount` int(4) default NULL COMMENT 'bank account(associates) through which payment is issued', `BCId` int(11) default NULL, `PaymentThroughLedger` int(11) default NULL, `DCLogId` int(11) default NULL, `tmpSelectedByUser` int(11) default NULL, `AllowAllUsersToView` tinyint(4) default '-1' COMMENT 'view this doc to all while browsing except than created/Last', PRIMARY KEY (`ID`), KEY `First_billID` (`ID`), KEY `First_billP_no` (`PeriodNo`), KEY `FKAssociate` (`AssociateSCPId`), KEY `Indbcid` (`BCId`) ) ENGINE=InnoDB AUTO_INCREMENT=9455 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; -- -- Table structure for cb_canebilltransactions -- CREATE TABLE `cb_canebilltransactions` ( `CaneBillTransactionId` int(11) NOT NULL auto_increment, `DocId` int(11) NOT NULL, `DocType` int(11) default NULL, `AgainstId` int(11) NOT NULL, `AgainstAmount` decimal(15,2) unsigned zerofill default '0.00 ', `LineRemark` varchar(300) collate utf8_unicode_ci default NULL, `CreatedBy` int(11) default NULL, `CreatedTimeStamp` datetime default NULL, `LastModifiedBy` int(11) default NULL, `LastModifiedTimeStamp` datetime default NULL, `Locked` tinyint(4) default NULL, `tmpTS` timestamp NULL default '1999-11-11 11:11:11', `BankId` int(11) default NULL, `LoanSchemeId` int(11) default NULL, `DetailsForExtras` varchar(30) collate utf8_unicode_ci default NULL, `AddOrSubstract`
Re: Help with query, (question simplified as last mail was very complicated to understand :))
Richard a écrit : Sorry about my last email which was long and not clear. This is what I want to do Join two tables on code table1 = code table3 where messageid = for example 28 table 1 contains : message from messageid -- message1 | code1 |28 message2 | code1 |28 message3 | code1 |28 message4 | code1 |29 table 2 contains name | code | num -- name1 | code2 | 1 name2 | code1 | 2 name3 | code1 | 3 If I do : SELECT a.message,,b.name FROM table1 a JOIN table2 b ON a.code=b.code WHERE a.id='28' I get : message| name --- message1 | name2 message2 | name2 message3 | name2 message1 | name3 message2 | name3 message3 | name3 But all I want to get is : message| name --- message1 | name3 message2 | name3 message3 | name3 If I do : SELECT * FROM table2 WHERE code = 'code1' ORDER BY num DESC LIMIT 1 I get : name | code | num -- name3 | code1 | 3 I now need to somehow combine the two to get : message| name --- message1 | name3 message2 | name3 message3 | name3 Of course I have simplified everything down to the minimum :) Thanks in advance, Richard As I have had no answer I presume that what I want to do is not possible or my question is not well explained. Anyhow I've rethought the system so I do not need to keep members information and now instead of adding a new entry I will now just change the existing one. I won't keep old members information in the database but I'll still have the database daily backups if I need the old information. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Help with query, (question simplified as last mail was very complicated to understand :))
the simple answer is and b.name='name3' ?Bon ChanceMartin__Disclaimer and confidentiality noteEverything in this e-mail and any attachments relates to the official business of Sender. This transmission is of a confidential nature and Sender does not endorse distribution to any party other than intended recipient. Sender does not necessarily endorse content contained within this transmission. Date: Sun, 30 Dec 2007 13:54:32 +0100 From: [EMAIL PROTECTED] To: mysql@lists.mysql.com Subject: Re: Help with query, (question simplified as last mail was very complicated to understand :)) Richard a écrit : Sorry about my last email which was long and not clear. This is what I want to do Join two tables on code table1 = code table3 where messageid = for example 28 table 1 contains : message from messageid -- message1 | code1 | 28 message2 | code1 | 28 message3 | code1 | 28 message4 | code1 | 29table 2 contains name | code | num -- name1 | code2 | 1 name2 | code1 | 2 name3 | code1 | 3 If I do : SELECT a.message,,b.name FROM table1 a JOIN table2 b ON a.code=b.code WHERE a.id='28' I get : message | name --- message1 | name2 message2 | name2 message3 | name2 message1 | name3 message2 | name3 message3 | name3But all I want to get is : message | name --- message1 | name3 message2 | name3 message3 | name3 If I do : SELECT * FROM table2 WHERE code = 'code1' ORDER BY num DESC LIMIT 1 I get : name | code | num -- name3 | code1 | 3 I now need to somehow combine the two to get : message | name --- message1 | name3 message2 | name3 message3 | name3 Of course I have simplified everything down to the minimum :) Thanks in advance, Richard As I have had no answer I presume that what I want to do is not possible or my question is not well explained. Anyhow I've rethought the system so I do not need to keep members information and now instead of adding a new entry I will now just change the existing one. I won't keep old members information in the database but I'll still have the database daily backups if I need the old information. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] _ The best games are on Xbox 360. Click here for a special offer on an Xbox 360 Console. http://www.xbox.com/en-US/hardware/wheretobuy/
Performance problem with ~0.5 GB tabel
Hi, I'm using phorum [1] and made some custom queries against their database. My query looks like this: SELECT message_id, subject, datestamp, forum_id, thread FROM phorum_messages WHERE forum_id IN (1, 2, 3, 4) AND parent_id = 0 AND thread != 0 AND status = 2 AND closed = 0 ORDER BY datestamp DESC LIMIT 3 The table phorum_message is about 500MB in size. The problem is that such a query often starts to hang in the Sorting result phase. This can take up to minutes and during this time problems really start: more and more such queries are coming in, each of them hanging for the same reason too and after a few minutes the maximum of connections are reached (currently 170) and everything is dead. Only killing the queries manually helps. My guess is that the filesort is problematic and so I tried to avoid it with the following things. When I use explain on the query I get back the following: id: 1 select_type: SIMPLE table: phorum_messages type: range possible_keys: thread_message, thread_forum, status_forum, list_page_float, list_page_flat, dup_check, last_post_time, forum_max_message, post_count key: post_count key_len: 9 ref: NULL rows: 1311 Extra: Using where; Using filesort When I remove the ORDER BY statements, the query is *not* using filesort. However, as you can guess, it is necessary. The goal of the query is to get the top-most posters in the selected forums. The MySQL documentation [2] says that under certain cases it should be possible to create appropriate keys so that even an ORDER BY can take advantage of, but I was unable to come up with such an. Is there a recommendation how to go for it? thanks, - Markus [1] http://www.phorum.org/ [2] http://dev.mysql.com/doc/refman/5.0/en/order-by-optimization.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Limit the results of a COUNT
Our search engine does a master query INNER JOINed to a series of COUNT (*) subqueries that return what the number of results would have been had the user chosen different filters (or no filter at all). As an example: SELECT Main.Col1, NoFilter.TotCount, Filter1.SubCount, etc. FROM ( SELECT Col1 FROM Table WHERE Col1 = X and Col2 Y and Col3 Z LIMIT 1, 30 ) Main INNER JOIN ( SELECT COUNT(*) AS TotCount FROM Table ) NoFilter INNER JOIN ( SELECT COUNT(*) AS SubCount WHERE Col2 Y ) Filter1 ETC. This query is being run against a database that currently as 100 Million records (and rapidly growing), and if TotCount is over about 50,000, the query is unacceptably slow. We need to LIMIT the subqueries to some maximum count (stop counting at, say, 50,000). Does anyone know a way to do this? Thanks -- View this message in context: http://www.nabble.com/Limit-the-results-of-a-COUNT-tp14549988p14549988.html Sent from the MySQL - General mailing list archive at Nabble.com. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
