RE: Erratic query performance
Any ideas of what could be causing the varied response time on a simple query when everything on the server appears to be identical from one run to another? Are there settings that can be made on the server to tweak response time for a database/query like this? [JS] Is it possible that there are locking conflicts? They can produce the kind of behavior you are describing. Here are stats on the files involved in the query and the actual query I am trying to run. Note that the number of receipts with amount = 1 is very smal compared to the total number of records. Main: 900,000 records, 500 Mb (886,361 where recordtype = INDIVIDUAL) Primary key: id (int) Receipt: 4,500,000 records, 700 Mb (6,817 where amount = 1) Primary key: id (int) Indexed on: mainid (int) Committee: 4,500 records, 600Kb (1,476 where party = D) Primary key: id (int) Indexed on: Comm_id (varchar(6)) create temporary table tmp type = heap select distinct 3 filterid, m.id, GroupLevel, 0 GroupCum from main m left join receipt r on m.id = r.mainid left join campaccommon.committee c on r.comm_id = c.Comm_id where recordtype = INDIVIDUAL and c.party = D and r.amount = 1 Returns 294 records. Thanks for any insight you can offer. - Leo Siefert -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: foreign keys: Cannot create InnoDB table
Hi, I can't create InnoDB table with foreign key constraints using more than 3 colmuns. When I create table `test_fk`.`tbl1`, it gives me: Can't create table 'test_fk.tbl1' (errno: 150) why? CREATE TABLE syntax looks perfectly right to me. Any suggestions are welcome. Thank you, wabi -- -- -- DDL CREATE TABLE IF NOT EXISTS `test_fk`.`tbl2` ( `col1` VARCHAR(2) NOT NULL , `col2` VARCHAR(2) NOT NULL , `col3` VARCHAR(2) NOT NULL , PRIMARY KEY (`col1`, `col2`, `col3`) ) ENGINE = InnoDB; CREATE TABLE IF NOT EXISTS `test_fk`.`tbl1` ( `tbl1_id` VARCHAR(12) NOT NULL , `col1` VARCHAR(2) NULL , `col2` VARCHAR(2) NULL , `col3` VARCHAR(2) NULL , PRIMARY KEY (`tbl1_id`) , INDEX `fk_test` (`col1` ASC, `col2` ASC, `col3` ASC) , CONSTRAINT `fk_test` FOREIGN KEY (`col1` , `col2` , `col3` ) REFERENCES `test_fk`.`tbl2` (`col1` , `col3` , `col2` ) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB; I guess your FK constraint needs the columns in the same order as the PK constraint. That is: col1, col2, col3 in the REFERENCES clause. With regards, Martijn Tonies Upscene Productions http://www.upscene.com Download Database Workbench for Oracle, MS SQL Server, Sybase SQL Anywhere, MySQL, InterBase, NexusDB and Firebird! Database questions? Check the forum: http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
foreign keys: Cannot create InnoDB table
Hi, All, I can't create InnoDB table with foreign key constraints using more than 3 colmuns. When I create table `test_fk`.`tbl1`, it gives me: Can't create table 'test_fk.tbl1' (errno: 150) why? CREATE TABLE syntax looks perfectly right to me. Any suggestions are welcome. Thank you, wabi -- -- -- DDL CREATE TABLE IF NOT EXISTS `test_fk`.`tbl2` ( `col1` VARCHAR(2) NOT NULL , `col2` VARCHAR(2) NOT NULL , `col3` VARCHAR(2) NOT NULL , PRIMARY KEY (`col1`, `col2`, `col3`) ) ENGINE = InnoDB; CREATE TABLE IF NOT EXISTS `test_fk`.`tbl1` ( `tbl1_id` VARCHAR(12) NOT NULL , `col1` VARCHAR(2) NULL , `col2` VARCHAR(2) NULL , `col3` VARCHAR(2) NULL , PRIMARY KEY (`tbl1_id`) , INDEX `fk_test` (`col1` ASC, `col2` ASC, `col3` ASC) , CONSTRAINT `fk_test` FOREIGN KEY (`col1` , `col2` , `col3` ) REFERENCES `test_fk`.`tbl2` (`col1` , `col3` , `col2` ) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB; -- -- -- mysql Output mysql SELECT VERSION(),NOW() FROM DUAL\G *** 1. row *** VERSION(): 5.1.31sp1-enterprise-gpl-advanced NOW(): 2009-08-14 18:04:00 1 row in set (0.00 sec) mysql DROP DATABASE `test_fk` ; ERROR 1008 (HY000): Can't drop database 'test_fk'; database doesn't exist mysql CREATE DATABASE IF NOT EXISTS `test_fk` ; Query OK, 1 row affected (0.00 sec) mysql SHOW WARNINGS; +---+--+---+ | Level | Code | Message | +---+--+---+ | Error | 1008 | Can't drop database 'test_fk'; database doesn't exist | +---+--+---+ 1 row in set (0.00 sec) mysql USE `test_fk`; Database changed mysql mysql DROP TABLE IF EXISTS `test_fk`.`tbl2` ; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql SHOW WARNINGS; +---+--+--+ | Level | Code | Message | +---+--+--+ | Note | 1051 | Unknown table 'tbl2' | +---+--+--+ 1 row in set (0.00 sec) mysql CREATE TABLE IF NOT EXISTS `test_fk`.`tbl2` ( - `col1` VARCHAR(2) NOT NULL , - `col2` VARCHAR(2) NOT NULL , - `col3` VARCHAR(2) NOT NULL , - PRIMARY KEY (`col1`, `col2`, `col3`) ) - ENGINE = InnoDB; Query OK, 0 rows affected (0.00 sec) mysql SHOW WARNINGS; Empty set (0.00 sec) mysql mysql DROP TABLE IF EXISTS `test_fk`.`tbl1` ; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql SHOW WARNINGS; +---+--+--+ | Level | Code | Message | +---+--+--+ | Note | 1051 | Unknown table 'tbl1' | +---+--+--+ 1 row in set (0.00 sec) mysql CREATE TABLE IF NOT EXISTS `test_fk`.`tbl1` ( - `tbl1_id` VARCHAR(12) NOT NULL , - `col1` VARCHAR(2) NULL , - `col2` VARCHAR(2) NULL , - `col3` VARCHAR(2) NULL , - PRIMARY KEY (`tbl1_id`) , - INDEX `fk_test` (`col1` ASC, `col2` ASC, `col3` ASC) , - CONSTRAINT `fk_test` - FOREIGN KEY (`col1` , `col2` , `col3` ) - REFERENCES `test_fk`.`tbl2` (`col1` , `col3` , `col2` ) - ON DELETE NO ACTION - ON UPDATE NO ACTION) - ENGINE = InnoDB; ERROR 1005 (HY000): Can't create table 'test_fk.tbl1' (errno: 150) mysql SHOW WARNINGS; +---+--++ | Level | Code | Message| +---+--++ | Error | 1005 | Can't create table 'test_fk.tbl1' (errno: 150) | +---+--++ 1 row in set (0.00 sec) mysql Bye # perror 150 MySQL error code 150: Foreign key constraint is incorrectly formed # -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Picking Collation Confusion
First off... I've read chapter 9.1.3 on character sets and collations and I'm still confused... :) (that could just be because today is Friday) Our application is installed at several different sites some running on Mac OS, some Windows and a few Linux which I suspect is what led to this situation. To deploy our app we basically do the following... 1. create and test 2. (on test server) mysqldump export.sql 3. (on deployment server) mysql export.sql Now I need to move a set of changes from the test server to the deployment server and I'm using mysqldiff to find the differences. I've noticed that 90% of the changes are simply to align the collation of fields and default collations for tables. Usually it's bouncing between utf8_general_ci and latin1_swedish_ci. 99.99% of the records in our various customers databases will be normal U.S. names and addresses but I know of a few customers that target their base fairly narrowly and might POTENTIALLY need to enter foreign names with accents and the like. Ultimately what it comes down to is... how worried should I be about making collations universal across at least a given customers instances of the application? (If not all copies of the application for all customers) I already have a routine that I call normalize database that makes sure default indexes are applied, etc... so it would be easy to add to that routine to check for and correct collations but then do I need to worry about existing data? Thanks for the advice! Matt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: foreign keys: Cannot create InnoDB table
Run: SHOW ENGINE INNODB STATUS \G And look for the LATEST FOREIGN KEY ERROR section. It'll explain the reason for the (errno: 150) message. Regards, Gavin Towey -Original Message- From: wabiko.takuma [mailto:wab...@sysrdc.ns-sol.co.jp] Sent: Friday, August 14, 2009 3:35 AM To: mysql@lists.mysql.com Subject: foreign keys: Cannot create InnoDB table Hi, All, I can't create InnoDB table with foreign key constraints using more than 3 colmuns. When I create table `test_fk`.`tbl1`, it gives me: Can't create table 'test_fk.tbl1' (errno: 150) why? CREATE TABLE syntax looks perfectly right to me. Any suggestions are welcome. Thank you, wabi -- -- -- DDL CREATE TABLE IF NOT EXISTS `test_fk`.`tbl2` ( `col1` VARCHAR(2) NOT NULL , `col2` VARCHAR(2) NOT NULL , `col3` VARCHAR(2) NOT NULL , PRIMARY KEY (`col1`, `col2`, `col3`) ) ENGINE = InnoDB; CREATE TABLE IF NOT EXISTS `test_fk`.`tbl1` ( `tbl1_id` VARCHAR(12) NOT NULL , `col1` VARCHAR(2) NULL , `col2` VARCHAR(2) NULL , `col3` VARCHAR(2) NULL , PRIMARY KEY (`tbl1_id`) , INDEX `fk_test` (`col1` ASC, `col2` ASC, `col3` ASC) , CONSTRAINT `fk_test` FOREIGN KEY (`col1` , `col2` , `col3` ) REFERENCES `test_fk`.`tbl2` (`col1` , `col3` , `col2` ) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB; -- -- -- mysql Output mysql SELECT VERSION(),NOW() FROM DUAL\G *** 1. row *** VERSION(): 5.1.31sp1-enterprise-gpl-advanced NOW(): 2009-08-14 18:04:00 1 row in set (0.00 sec) mysql DROP DATABASE `test_fk` ; ERROR 1008 (HY000): Can't drop database 'test_fk'; database doesn't exist mysql CREATE DATABASE IF NOT EXISTS `test_fk` ; Query OK, 1 row affected (0.00 sec) mysql SHOW WARNINGS; +---+--+---+ | Level | Code | Message | +---+--+---+ | Error | 1008 | Can't drop database 'test_fk'; database doesn't exist | +---+--+---+ 1 row in set (0.00 sec) mysql USE `test_fk`; Database changed mysql mysql DROP TABLE IF EXISTS `test_fk`.`tbl2` ; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql SHOW WARNINGS; +---+--+--+ | Level | Code | Message | +---+--+--+ | Note | 1051 | Unknown table 'tbl2' | +---+--+--+ 1 row in set (0.00 sec) mysql CREATE TABLE IF NOT EXISTS `test_fk`.`tbl2` ( - `col1` VARCHAR(2) NOT NULL , - `col2` VARCHAR(2) NOT NULL , - `col3` VARCHAR(2) NOT NULL , - PRIMARY KEY (`col1`, `col2`, `col3`) ) - ENGINE = InnoDB; Query OK, 0 rows affected (0.00 sec) mysql SHOW WARNINGS; Empty set (0.00 sec) mysql mysql DROP TABLE IF EXISTS `test_fk`.`tbl1` ; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql SHOW WARNINGS; +---+--+--+ | Level | Code | Message | +---+--+--+ | Note | 1051 | Unknown table 'tbl1' | +---+--+--+ 1 row in set (0.00 sec) mysql CREATE TABLE IF NOT EXISTS `test_fk`.`tbl1` ( - `tbl1_id` VARCHAR(12) NOT NULL , - `col1` VARCHAR(2) NULL , - `col2` VARCHAR(2) NULL , - `col3` VARCHAR(2) NULL , - PRIMARY KEY (`tbl1_id`) , - INDEX `fk_test` (`col1` ASC, `col2` ASC, `col3` ASC) , - CONSTRAINT `fk_test` - FOREIGN KEY (`col1` , `col2` , `col3` ) - REFERENCES `test_fk`.`tbl2` (`col1` , `col3` , `col2` ) - ON DELETE NO ACTION - ON UPDATE NO ACTION) - ENGINE = InnoDB; ERROR 1005 (HY000): Can't create table 'test_fk.tbl1' (errno: 150) mysql SHOW WARNINGS; +---+--++ | Level | Code | Message| +---+--++ | Error | 1005 | Can't create table 'test_fk.tbl1' (errno: 150) | +---+--++ 1 row in set (0.00 sec) mysql Bye # perror 150 MySQL error code 150: Foreign key constraint is incorrectly formed # -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=gto...@ffn.com The information contained in this transmission may contain privileged and confidential information. It is intended only for the use of the person(s) named above. If you are not the intended recipient, you are hereby notified that any review, dissemination, distribution or duplication of this communication is strictly prohibited. If you are not the intended recipient, please contact the sender by reply email and destroy all copies of the original message. -- MySQL General Mailing
RE: Picking Collation Confusion
Hi Matt, You need to worry about consistent collations if you want consistent behavior for sorting and comparing fields. That sounds pretty important to me. Note that latin1 can hold accented characters as well. Regards, Gavin Towey -Original Message- From: Matt Neimeyer [mailto:m...@neimeyer.org] Sent: Friday, August 14, 2009 9:52 AM To: mysql@lists.mysql.com Subject: Picking Collation Confusion First off... I've read chapter 9.1.3 on character sets and collations and I'm still confused... :) (that could just be because today is Friday) Our application is installed at several different sites some running on Mac OS, some Windows and a few Linux which I suspect is what led to this situation. To deploy our app we basically do the following... 1. create and test 2. (on test server) mysqldump export.sql 3. (on deployment server) mysql export.sql Now I need to move a set of changes from the test server to the deployment server and I'm using mysqldiff to find the differences. I've noticed that 90% of the changes are simply to align the collation of fields and default collations for tables. Usually it's bouncing between utf8_general_ci and latin1_swedish_ci. 99.99% of the records in our various customers databases will be normal U.S. names and addresses but I know of a few customers that target their base fairly narrowly and might POTENTIALLY need to enter foreign names with accents and the like. Ultimately what it comes down to is... how worried should I be about making collations universal across at least a given customers instances of the application? (If not all copies of the application for all customers) I already have a routine that I call normalize database that makes sure default indexes are applied, etc... so it would be easy to add to that routine to check for and correct collations but then do I need to worry about existing data? Thanks for the advice! Matt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=gto...@ffn.com The information contained in this transmission may contain privileged and confidential information. It is intended only for the use of the person(s) named above. If you are not the intended recipient, you are hereby notified that any review, dissemination, distribution or duplication of this communication is strictly prohibited. If you are not the intended recipient, please contact the sender by reply email and destroy all copies of the original message. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
