Re: Re: Performance Question And Problem
At 10:47 PM 11/23/2006, John Kopanas wrote: That is awesome... thanks. I still am not sure exactly though why this take 2 seconds while my methond took over a minute for the same amount of rows. In essence don't the two methods do the same things? No. Your approach was executing the subquery 2000 times for the 2000 records in your company file. And will run 500,000 times when you go to production data. Somebody with better math than I should try to project that. His prep query runs once, and his update query runs once. Scales very nicely. Barry Newton -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Re: Performance Question And Problem
At 09:47 PM 11/23/2006, John Kopanas wrote: That is awesome... thanks. I still am not sure exactly though why this take 2 seconds while my methond took over a minute for the same amount of rows. In essence don't the two methods do the same things? The Group By executes in one operation. I may be wrong, but I think your SQL subselect has to be re-executed for each Company_Id that it finds. Joins are usually faster than a subselect and a memory table makes the joins faster. BTW, if more than 1 person will be running this SQL at the same time, then you can use TEMPORARY table with MEMORY so each session gets their own memory table. Glad it works. Mike (Please don't send me a service change, got enough of those already.) On 11/23/06, mos <[EMAIL PROTECTED]> wrote: At 05:50 PM 11/23/2006, you wrote: >I have the following query: > > UPDATE companies c > SET >total_annual_service_charge = > ( >SELECT SUM(annual_service_charge) >FROM purchased_services ps WHERE ps.company_id = c.id > ); > > >It takes 1s to run when I have two tables of 500 rows, 4s with two >tables of 1000 rows, 15s to run with two tables to run with 2000 rows, >90s for two tables of 5000 rows. This is ridiculous. And I need to >run it on two tables of approx. 500,000 rows. I need a better >solution. > >And there is an index on ps.company_id and c.id. Any suggestions on >how I can improve my query? > >-- >John Kopanas >[EMAIL PROTECTED] John, I would split it into separate sql statements. 1) Create a memory table of the totals: drop table if exists CompanyTotals; create table CompanyTotals type=memory select Company_Id Id, SUM(annual_service_charge) ServCharge FROM purchased_services ps group by Company_Id; alter table CompanyTotals add index ix_Id (Id); 2) Update the Companies table with the CompanyTotals: update Companies C, CompanyTotals CT set total_annual_service_charge=CT.ServCharge where C.Id=CT.Id; This should be much faster. Mike >http://www.kopanas.com >http://www.cusec.net >http://www.soen.info > >-- >MySQL General Mailing List >For list archives: http://lists.mysql.com/mysql >To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- John Kopanas [EMAIL PROTECTED] http://www.kopanas.com http://www.cusec.net http://www.soen.info -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Re: Performance Question And Problem
That is awesome... thanks. I still am not sure exactly though why this take 2 seconds while my methond took over a minute for the same amount of rows. In essence don't the two methods do the same things? On 11/23/06, mos <[EMAIL PROTECTED]> wrote: At 05:50 PM 11/23/2006, you wrote: >I have the following query: > > UPDATE companies c > SET >total_annual_service_charge = > ( >SELECT SUM(annual_service_charge) >FROM purchased_services ps WHERE ps.company_id = c.id > ); > > >It takes 1s to run when I have two tables of 500 rows, 4s with two >tables of 1000 rows, 15s to run with two tables to run with 2000 rows, >90s for two tables of 5000 rows. This is ridiculous. And I need to >run it on two tables of approx. 500,000 rows. I need a better >solution. > >And there is an index on ps.company_id and c.id. Any suggestions on >how I can improve my query? > >-- >John Kopanas >[EMAIL PROTECTED] John, I would split it into separate sql statements. 1) Create a memory table of the totals: drop table if exists CompanyTotals; create table CompanyTotals type=memory select Company_Id Id, SUM(annual_service_charge) ServCharge FROM purchased_services ps group by Company_Id; alter table CompanyTotals add index ix_Id (Id); 2) Update the Companies table with the CompanyTotals: update Companies C, CompanyTotals CT set total_annual_service_charge=CT.ServCharge where C.Id=CT.Id; This should be much faster. Mike >http://www.kopanas.com >http://www.cusec.net >http://www.soen.info > >-- >MySQL General Mailing List >For list archives: http://lists.mysql.com/mysql >To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- John Kopanas [EMAIL PROTECTED] http://www.kopanas.com http://www.cusec.net http://www.soen.info -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Performance Question And Problem
In the last episode (Nov 23), John Kopanas said: > I have the following query: > > UPDATE companies c > SET >total_annual_service_charge = > ( >SELECT SUM(annual_service_charge) >FROM purchased_services ps WHERE ps.company_id = c.id > ); > > > It takes 1s to run when I have two tables of 500 rows, 4s with two > tables of 1000 rows, 15s to run with two tables to run with 2000 > rows, 90s for two tables of 5000 rows. This is ridiculous. And I > need to run it on two tables of approx. 500,000 rows. I need a > better solution. > > And there is an index on ps.company_id and c.id. Any suggestions on > how I can improve my query? If you're I/O bound during this query, try an index on (company_id,annual_service_charge) on your purchased_services table. That'll let the subquery complete using just an index scan. If that doesn't help, try mos's idea. -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Performance Question And Problem
At 05:50 PM 11/23/2006, you wrote: I have the following query: UPDATE companies c SET total_annual_service_charge = ( SELECT SUM(annual_service_charge) FROM purchased_services ps WHERE ps.company_id = c.id ); It takes 1s to run when I have two tables of 500 rows, 4s with two tables of 1000 rows, 15s to run with two tables to run with 2000 rows, 90s for two tables of 5000 rows. This is ridiculous. And I need to run it on two tables of approx. 500,000 rows. I need a better solution. And there is an index on ps.company_id and c.id. Any suggestions on how I can improve my query? -- John Kopanas [EMAIL PROTECTED] John, I would split it into separate sql statements. 1) Create a memory table of the totals: drop table if exists CompanyTotals; create table CompanyTotals type=memory select Company_Id Id, SUM(annual_service_charge) ServCharge FROM purchased_services ps group by Company_Id; alter table CompanyTotals add index ix_Id (Id); 2) Update the Companies table with the CompanyTotals: update Companies C, CompanyTotals CT set total_annual_service_charge=CT.ServCharge where C.Id=CT.Id; This should be much faster. Mike http://www.kopanas.com http://www.cusec.net http://www.soen.info -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Performance Question And Problem
I have the following query: UPDATE companies c SET total_annual_service_charge = ( SELECT SUM(annual_service_charge) FROM purchased_services ps WHERE ps.company_id = c.id ); It takes 1s to run when I have two tables of 500 rows, 4s with two tables of 1000 rows, 15s to run with two tables to run with 2000 rows, 90s for two tables of 5000 rows. This is ridiculous. And I need to run it on two tables of approx. 500,000 rows. I need a better solution. And there is an index on ps.company_id and c.id. Any suggestions on how I can improve my query? -- John Kopanas [EMAIL PROTECTED] http://www.kopanas.com http://www.cusec.net http://www.soen.info -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Master Switch (Or Write by SQL_THREAD only)
Hi, I'm currently looking at te best way to do a master switch in a replication setup (4.1). Let's say I'm switching from M1 to M2. I'm looking particularly at the different possible way to hold the query (Write) on the M2 server till I can check that the replicate was done playing the query on M1... If I do a FLUSH TABLES WITH READ LOCK; the SQL_THREAD will be also block so it will never catch up... Is there a way to allow the SQL_THREAD to write while holding everything else ? Best Regards, PS: I know about MySQL 5.0 multi-master setup :) just not the case at the moment -- Math aka ROunofF argontechnologies.ca -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to keep myqsl backup server
Hi friends, I need to create an application where i will be requiring a backup server like when one server fails i can switch automatically to the other server hosted somewhere else , now the data needs to be consistent on both the servers, one sol is i keep automated backups on the primary server and restore it by a script on the backup server, but i need to know how will i switch between servers and if i use domain names instead of ips how will i make immediate switch. I know some part of the question is not appropriate for this mailing list but pl help. Regards, Abhishek jain
AW: Copy some datasets including the foreign key constraints
Hello Jerry, I can give you the shema of my "parent" table: CREATE TABLE `demo_employee` (\n `idemployee` int(10) unsigned NOT NULL auto_increment,\n `demo_mandt_idmandt` int(10) unsigned NOT NULL default '0',\n `demo_adress_idadress` int(10) unsigned NOT NULL default '0',\n `demo_adress_demo_region_idregion` int(10) unsigned NOT NULL default '0',\n `demo_adress_demo_country_idcountry` int(10) unsigned NOT NULL default '0',\n `demo_biz_role_idbiz_role` int(10) unsigned NOT NULL default '0',\n `demo_job_idjob` int(10) unsigned NOT NULL default '0',\n `demo_user_iduser` int(10) unsigned NOT NULL default '0',\n `I_EE_EMPLOYEE_ID` varchar(25) collate utf8_unicode_ci default NULL,\n `I_EE_CO_GIVEN_NAME` varchar(25) collate utf8_unicode_ci default NULL,\n `I_EE_CO_MIDDLE_NAME` varchar(25) collate utf8_unicode_ci default NULL,\n `I_EE_CO_FAMILY_NAME` varchar(25) collate utf8_unicode_ci default NULL,\n `I_EE_CO_INITIALS_NAME` varchar(25) collate utf8_unicode_ci default NULL,\n `I_EE_BANK_ACCOUNT_HOLD` varchar(25) collate utf8_unicode_ci default NULL,\n `I_EE_BANK_ACCOUNT_ID` varchar(25) collate utf8_unicode_ci default NULL,\n `I_EE_WK_PO_AD_FLO_ID` varchar(25) collate utf8_unicode_ci default NULL,\n `I_EE_WK_PO_AD_ROOM_ID` varchar(25) collate utf8_unicode_ci default NULL,\n `I_PEH_START_DATE_HIRING` varchar(25) collate utf8_unicode_ci default NULL,\n `I_PEH_END_DATE_HIRING` varchar(25) collate utf8_unicode_ci default NULL,\n `I_PEH_EVENT_TYPE_CODE` varchar(25) collate utf8_unicode_ci default NULL,\n `I_WA_TYPE_CODE` varchar(25) collate utf8_unicode_ci default NULL,\n `I_WA_ADMIN_CATEGORY_CODE` varchar(25) collate utf8_unicode_ci default NULL,\n `I_COMPANY_ID` int(10) unsigned default NULL,\n `I_PEH_POS_ASS_BEG_DATE` varchar(25) collate utf8_unicode_ci default NULL,\n `I_WA_POSITION_MAIN_INDICATOR` varchar(25) collate utf8_unicode_ci default NULL,\n `I_WA_POS_ASSIGNMENT_PERCENT` varchar(25) collate utf8_unicode_ci default NULL,\n `I_WA_AWT_RATE_FIRST_VALUE` varchar(25) collate utf8_unicode_ci default NULL,\n `I_WA_AWT_RATE_FIRST_UNIT` varchar(25) collate utf8_unicode_ci default NULL,\n `I_WA_AWT_RATE_SEC_VALUE` varchar(25) collate utf8_unicode_ci default NULL,\n `I_WA_AWT_RATE_SEV_UNIT` varchar(25) collate utf8_unicode_ci default NULL,\n `I_WA_NOTICE_PERIOD` varchar(25) collate utf8_unicode_ci default NULL,\n `I_EE_CO_GENDER_CODE` int(10) unsigned default NULL,\n `I_EE_CO_MATERIAL_STATUS_CODE` int(10) unsigned default NULL,\n `I_EE_CO_FROM_OF_ADRESS_CODE` int(10) unsigned default NULL,\n `I_EE_CO_ACADEMIC_TITLE_CODE` int(10) unsigned default NULL,\n `I_EE_CO_BIRTH_PLACE_NAME` varchar(45) collate utf8_unicode_ci default NULL,\n `I_EE_CO_BIRTH_NAME` varchar(25) collate utf8_unicode_ci default NULL,\n `I_EE_CO_BIRTH_DATE` varchar(25) collate utf8_unicode_ci default NULL,\n `I_EE_CO_ADDITIONAL_ACA_TITLE` varchar(25) collate utf8_unicode_ci default NULL,\n `I_EE_HO_ADD_USAGE_DEFAULT` varchar(25) collate utf8_unicode_ci default NULL,\n `I_EE_HO_ADD_USAGE` varchar(25) collate utf8_unicode_ci default NULL,\n `I_EE_WK_ADD_USAGE` varchar(25) collate utf8_unicode_ci default NULL,\n `demo_user` int(10) unsigned default NULL,\n PRIMARY KEY (`idemployee`,`demo_mandt_idmandt`,`demo_adress_idadress`,`demo_adress_demo_region_idregion`,`demo_adress_demo_country_idcountry`,`demo_biz_role_idbiz_role`,`demo_job_idjob`,`demo_user_iduser`),\n KEY `demo_employee_FKIndex1` (`demo_mandt_idmandt`),\n KEY `demo_employee_FKIndex2` (`demo_adress_idadress`,`demo_adress_demo_region_idregion`,`demo_adress_demo_country_idcountry`),\n KEY `demo_employee_FKIndex3` (`demo_biz_role_idbiz_role`),\n KEY `demo_employee_FKIndex4` (`demo_job_idjob`),\n KEY `demo_employee_FKIndex5` (`demo_user_iduser`),\n CONSTRAINT `demo_employee_ibfk_1` FOREIGN KEY (`demo_mandt_idmandt`) REFERENCES `demo_mandt` (`idmandt`) ON DELETE CASCADE ON UPDATE CASCADE,\n CONSTRAINT `demo_employee_ibfk_2` FOREIGN KEY (`demo_adress_idadress`, `demo_adress_demo_region_idregion`, `demo_adress_demo_country_idcountry`) REFERENCES `demo_adress` (`idadress`, `demo_region_idregion`, `demo_country_idcountry`) ON DELETE NO ACTION ON UPDATE NO ACTION,\n CONSTRAINT `demo_employee_ibfk_3` FOREIGN KEY (`demo_biz_role_idbiz_role`) REFERENCES `demo_biz_role` (`idbiz_role`) ON DELETE CASCADE ON UPDATE CASCADE,\n CONSTRAINT `demo_employee_ibfk_4` FOREIGN KEY (`demo_job_idjob`) REFERENCES `demo_job` (`idjob`) ON DELETE CASCADE ON UPDATE CASCADE,\n CONSTRAINT `demo_employee_ibfk_5` FOREIGN KEY (`demo_user_iduser`) REFERENCES `demo_user` (`iduser`) ON DELETE CASCADE ON UPDATE CASCADE\n) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci Know i'm searching an easy way to copy the "parent" table an all his childs but with the new key values. Currently i do this via an PHP script, but it is a lot of work and not very flexible. Dosen't provide mysql any easy function ? Kind regards -Ursprüngliche Nachricht- Von: Jerry Schwartz
Replication error: Unable to get certificate
Hi
I've followed the replication procedure, as instructed on the online
manual.
I want to use replication over SSL.
I've followed this procedure to create test SSL certificates, keys, etc.
http://dev.mysql.com/doc/refman/4.1/en/secure-create-certs.html
On slave machine:
mysql> show slave status\G
*** 1. row ***
Slave_IO_State: Waiting for master to send event
Master_User: repl
Connect_Retry: 60
Master_Log_File: amit0-bin.60
Read_Master_Log_Pos: 79
Relay_Log_File: core1-relay-bin.03
Relay_Log_Pos: 337
Relay_Master_Log_File: amit0-bin.60
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: overdrive
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 79
Relay_Log_Space: 337
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: Yes
Master_SSL_CA_File: /root/.mysql/cacert.pem
Master_SSL_CA_Path: /root/.mysql/
Master_SSL_Cert: /root/.mysql/client-cert.pem
Master_SSL_Cipher:
Master_SSL_Key: /root/.mysql/client-key.pem
Seconds_Behind_Master: 0
# mysql --version
mysql Ver 14.7 Distrib 4.1.21, for pc-linux-gnu (i686) using readline 5.1
Replication seems to work. It's even using SSL. However I'm getting the
following error in log:
061123 10:30:53 [Note] Slave SQL thread initialized, starting
replication in log 'amit0-bin.60' at position 79, relay log
'./core1-relay-bin.03' position: 294
Error when connection to server using
SSL:3017771936:error:0200100D:system library:fopen:Permission
denied:bss_file.c:352:fopen('/root/.mysql/client-cert.pem','r')
3017771936:error:20074002:BIO routines:FILE_CTRL:system lib:bss_file.c:354:
3017771936:error:140AD002:SSL
routines:SSL_CTX_use_certificate_file:system lib:ssl_rsa.c:470:
Unable to get certificate from '/root/.mysql/client-cert.pem'
Why is this error message generated? How come replication is happening
and IS secured after all?
I've found this bug: http://bugs.mysql.com/bug.php?id=11169
Is this the same issue? As I'm currently using a lesser version than
that for-which this bug is reported, can I assume an upgrade will solve it?
Thanks, Amit
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Weird error with stored function
Hi, I am trying to use stored functions through the prepared statement C API. I have one application that issues relatively long running queries, while the other is doing some inserts, updates, and short queries. If the second application somehow calls a stored function (either directly through a prepared statement, or indirectly through a trigger) while first is in the middle of a query, then I receiving an error like this one: FUNCTION gi.get_country does not exist Anybody have seen something like this before? How can I circumvent it? I'm on windows, running MySQL 5.0.27-community-log. I am using InnoDB for my database, and the server SQL mode is sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,ANS I,ONLY_FULL_GROUP_BY" Thanks in advance ImRe -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
