RE: query tuning
Good advices indeed. Thanks for sharing. iñ On Wed, 14 Nov 2012, Rick James wrote: A PRIMARY KEY is a KEY, so the second of these is redundant: PRIMARY KEY (`zzk`), KEY `zzk` (`zzk`), "Compound" indexes are your friend. Learn from http://mysql.rjweb.org/doc.php/index1 `zzr_StatusTime` mediumtext, `zzr_StatusDate` mediumtext, Don't need 16MB for a date or time. Combining the fields is usually a better idea. VARCHAR(255), and especially TEXT/MEDIUMTEXT, can hurt performance when temp tables needed in a query -- use something sensible. `NameLastFirstMI` varchar(255) DEFAULT NULL, 1 letter? Occupying up to 767 bytes (255 utf8 chars)! InnoDB is generally faster than MyISAM. KEY `isdigitized` (`IsDigitized`), Rarely is a 'flag' (yes/no) value worth indexing. `is_deleted` int(11) DEFAULT NULL, If it is just a 0/1 flag, then consider TINYINT UNSIGNED NOT NULL -- it will shrink the space for that field significantly. `Q01_answer` text, `Q01_title` text, `Q02_answer` text, `Q02_title` text, Generally better to have another table for "arrays"; in this case it might have 4 columns: Appt_zzk, question_number, answer, title. and multiple rows for each Appt. ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='InnoDB free: 4068352 kB' Sounds confused about the engine?? Most fields are NULLable. Not a serious issue, but not good practice. As you add INDEX(ProcModeCode, dateexam) you can DROP this as being redundant: KEY `ProcModeCode` (`ProcModeCode`) The quick fix is to try the index suggested. It should change from 1 SIMPLE e index dateexam,IdAppt,ProcModeCodePRIMARY 4 NULL 1121043 "Using where" Note the huge estimated number of rows. That should decline significantly. What's the value of key_buffer_size? It should generally be 20% of _available_ RAM (for MyISAM usage). -Original Message- From: James W. McNeely [mailto:jmcne...@nwrads.com] Sent: Wednesday, November 14, 2012 3:48 PM To: mysql@lists.mysql.com Subject: Re: query tuning Rick, Thanks! Here is the create table info. Sorry for the size, but you can skip down from all the fields to see the indexes. CREATE TABLE `exams` ( `zAddDate` date DEFAULT NULL, `zModDate` date DEFAULT NULL, `IdPatient` varchar(32) DEFAULT NULL, `zModBy` varchar(255) DEFAULT NULL, `Notes` mediumtext, `TechComments` mediumtext, `NameLastFirstMI` varchar(255) DEFAULT NULL, `IdExam` varchar(32) DEFAULT NULL, `IdAppt` varchar(255) DEFAULT NULL, `IdProcedure` varchar(255) DEFAULT NULL, `ProcName` mediumtext, `zzr_StatusBy` mediumtext, `zzr_StatusTime` mediumtext, `zzr_StatusDate` mediumtext, `zAddBy` varchar(255) DEFAULT NULL, `zMasterUserTest` varchar(255) DEFAULT NULL, `ProcAreaCode` mediumtext, `ClinicalICD9_1` mediumtext, `ICD9Text` mediumtext, `FilmsStandard` mediumtext, `FilmsAlternate` mediumtext, `OutsideSource` mediumtext, `ProcCode` mediumtext, `ClinicalText` mediumtext, `OutsideStatus` mediumtext, `OutsideDate` varchar(255) DEFAULT NULL, `ProcModeCode` varchar(16) DEFAULT NULL, `DateOfBirth` varchar(255) DEFAULT NULL, `PathDiagnosis` mediumtext, `PathSize` mediumtext, `PathBiopsy` mediumtext, `PathBiopsyDate` varchar(255) DEFAULT NULL, `ClinicalICD9_2` mediumtext, `ClinicalICD9_3` mediumtext, `RefPractInstructions` mediumtext, `_External` mediumtext, `FindCode` mediumtext, `NWRaccession` varchar(255) DEFAULT NULL, `Gender` varchar(255) DEFAULT NULL, `IdOverReadFacility` varchar(255) DEFAULT NULL, `ProcDorS` mediumtext, `CompareToDate` varchar(255) DEFAULT NULL, `IdRefSite` varchar(255) DEFAULT NULL, `IsBillable` varchar(255) DEFAULT NULL, `LastWordProcRt` mediumtext, `IdPerformedBy` varchar(255) DEFAULT NULL, `ContrastRiskFactors` mediumtext, `TimeToCompletion` varchar(255) DEFAULT NULL, `ContrastDose` mediumtext, `OutsideSourceOther` mediumtext, `SiteSide` mediumtext, `SiteLocation` mediumtext, `OverReadExamNumber` varchar(255) DEFAULT NULL, `IsOverRead` varchar(255) DEFAULT NULL, `OverReadCharge` varchar(255) DEFAULT NULL, `IsBillIns` varchar(255) DEFAULT NULL, `LastWordProcLt` mediumtext, `IsRLB` varchar(255) DEFAULT NULL, `IsBCHP` varchar(255) DEFAULT NULL, `IsNWRCoordUSOverride` varchar(255) DEFAULT NULL, `IsTechOnly` varchar(255) DEFAULT NULL, `IsProcNotComplete` varchar(255) DEFAULT NULL, `IsWaiverSigned` varchar(255) DEFAULT NULL, `IdInsur1` varchar(255) DEFAULT NULL, `IdInsur2` varchar(255) DEFAULT NULL, `IsNoInsurance` varchar(255) DEFAULT NULL, `IsPrintBillInfo` varchar(255) DEFAULT NULL, `OverReadChargePrinted` varchar(255) DEFAULT NULL, `RefSiteOther` varchar(255) DEFAULT NULL, `IsOverReadBillIns Copy` varchar(255) DEFAULT NULL, `ExamRoom` varchar(255) DEFAULT NULL, `IdProtocol` varchar(255) DEFAULT NULL, `IsTransDelayed` varchar(255) DEFAULT NULL, `Patient Shielded` varchar(255) DEFAULT NULL, `Patient Permitted` varchar(255
Re: query tuning
Rick, This is incredibly useful, thanks for taking the time to share with me. I am really learning stuff here! Thanks, Jim On Nov 14, 2012, at 5:12 PM, Rick James wrote: > A PRIMARY KEY is a KEY, so the second of these is redundant: >> PRIMARY KEY (`zzk`), >> KEY `zzk` (`zzk`), > > "Compound" indexes are your friend. Learn from > http://mysql.rjweb.org/doc.php/index1 > >> `zzr_StatusTime` mediumtext, >> `zzr_StatusDate` mediumtext, > Don't need 16MB for a date or time. > Combining the fields is usually a better idea. > > VARCHAR(255), and especially TEXT/MEDIUMTEXT, can hurt performance when temp > tables needed in a query -- use something sensible. >> `NameLastFirstMI` varchar(255) DEFAULT NULL, > 1 letter? Occupying up to 767 bytes (255 utf8 chars)! > > InnoDB is generally faster than MyISAM. > >> KEY `isdigitized` (`IsDigitized`), > Rarely is a 'flag' (yes/no) value worth indexing. >> `is_deleted` int(11) DEFAULT NULL, > If it is just a 0/1 flag, then consider TINYINT UNSIGNED NOT NULL -- it will > shrink the space for that field significantly. > >> `Q01_answer` text, >> `Q01_title` text, >> `Q02_answer` text, >> `Q02_title` text, > Generally better to have another table for "arrays"; in this case it might > have 4 columns: > Appt_zzk, question_number, answer, title. > and multiple rows for each Appt. > >> ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='InnoDB free: 4068352 kB' > Sounds confused about the engine?? > > Most fields are NULLable. Not a serious issue, but not good practice. > > As you add > INDEX(ProcModeCode, dateexam) > you can DROP this as being redundant: > KEY `ProcModeCode` (`ProcModeCode`) > > The quick fix is to try the index suggested. It should change from >> 1 SIMPLE e index dateexam,IdAppt,ProcModeCodePRIMARY 4 >> NULL1121043 "Using where" > Note the huge estimated number of rows. That should decline significantly. > > What's the value of key_buffer_size? It should generally be 20% of > _available_ RAM (for MyISAM usage). > >> -Original Message- >> From: James W. McNeely [mailto:jmcne...@nwrads.com] >> Sent: Wednesday, November 14, 2012 3:48 PM >> To: mysql@lists.mysql.com >> Subject: Re: query tuning >> >> Rick, >> >> Thanks! Here is the create table info. Sorry for the size, but you can skip >> down from all the fields to see the indexes. >> >> CREATE TABLE `exams` ( >> `zAddDate` date DEFAULT NULL, >> `zModDate` date DEFAULT NULL, >> `IdPatient` varchar(32) DEFAULT NULL, >> `zModBy` varchar(255) DEFAULT NULL, >> `Notes` mediumtext, >> `TechComments` mediumtext, >> `NameLastFirstMI` varchar(255) DEFAULT NULL, >> `IdExam` varchar(32) DEFAULT NULL, >> `IdAppt` varchar(255) DEFAULT NULL, >> `IdProcedure` varchar(255) DEFAULT NULL, >> `ProcName` mediumtext, >> `zzr_StatusBy` mediumtext, >> `zzr_StatusTime` mediumtext, >> `zzr_StatusDate` mediumtext, >> `zAddBy` varchar(255) DEFAULT NULL, >> `zMasterUserTest` varchar(255) DEFAULT NULL, >> `ProcAreaCode` mediumtext, >> `ClinicalICD9_1` mediumtext, >> `ICD9Text` mediumtext, >> `FilmsStandard` mediumtext, >> `FilmsAlternate` mediumtext, >> `OutsideSource` mediumtext, >> `ProcCode` mediumtext, >> `ClinicalText` mediumtext, >> `OutsideStatus` mediumtext, >> `OutsideDate` varchar(255) DEFAULT NULL, >> `ProcModeCode` varchar(16) DEFAULT NULL, >> `DateOfBirth` varchar(255) DEFAULT NULL, >> `PathDiagnosis` mediumtext, >> `PathSize` mediumtext, >> `PathBiopsy` mediumtext, >> `PathBiopsyDate` varchar(255) DEFAULT NULL, >> `ClinicalICD9_2` mediumtext, >> `ClinicalICD9_3` mediumtext, >> `RefPractInstructions` mediumtext, >> `_External` mediumtext, >> `FindCode` mediumtext, >> `NWRaccession` varchar(255) DEFAULT NULL, >> `Gender` varchar(255) DEFAULT NULL, >> `IdOverReadFacility` varchar(255) DEFAULT NULL, >> `ProcDorS` mediumtext, >> `CompareToDate` varchar(255) DEFAULT NULL, >> `IdRefSite` varchar(255) DEFAULT NULL, >> `IsBillable` varchar(255) DEFAULT NULL, >> `LastWordProcRt` mediumtext, >> `IdPerformedBy` varchar(255) DEFAULT NULL, >> `ContrastRiskFactors` mediumtext, >> `TimeToCompletion` varchar(255) DEFAULT NULL, >> `ContrastDose` mediumtext, >> `OutsideSourceOther` mediumtext, >> `SiteSide` mediumtext, >> `SiteLocation` mediumtext, >> `OverReadExamNumber` varchar(255) DEFAULT NULL, >> `IsOverRead` varchar(255) DEFAULT NULL, >> `OverReadCharge` varchar(255) DEFAULT NULL, >> `IsBillIns` varchar(255) DEFAULT NULL, >> `LastWordProcLt` mediumtext, >> `IsRLB` varchar(255) DEFAULT NULL, >> `IsBCHP` varchar(255) DEFAULT NULL, >> `IsNWRCoordUSOverride` varchar(255) DEFAULT NULL, >> `IsTechOnly` varchar(255) DEFAULT NULL, >> `IsProcNotComplete` varchar(255) DEFAULT NULL, >> `IsWaiverSigned` varchar(255) DEFAULT NULL, >> `IdInsur1` varchar(255) DEFAULT NULL, >> `IdInsur2` varchar(255) DEFAULT NULL, >> `IsNoInsurance` varchar(255) DEFAULT NULL, >> `IsPrintBillInfo` varchar(255) DEFAULT NULL, >> `OverRead
Re: Issue regarding the import of the date from csv file to the table in the database in mysql
2012/11/15 00:30 +0100, Mogens Melander I guess I'm sill learning. Does that mean that, if the last column in a load blabla. is a -00-00 terminated by ^n it might error ? Or are we talking ODBC ? Find it under LOAD DATA If an empty field is parsed for a NOT NULL DATE or DATETIME, instead of reporting error as in strict mode, the parser makes the date -00-00--Maybe in ODBC, too, which I do not know, but certainly from character input. (I regularly use CSV files with MySQL, although the match is not perfect.) This is a particular case of "zero" (see LOAD DATA) for any NOT NULL type. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Replication between different versions
Am 15.11.2012 12:46, schrieb Manuel Arostegui: > 2012/11/15 Lorenzo Milesi > >> Hi. >> >> Is it possible to have a master/master replication between servers of >> different versions? >> Now I've two 5.0.51 syncing themselves. I'm installing a new server with >> 5.5.28 and I'd like if I can upgrade but still mantaining compatibility. >> Otherwise I'll keep on installing the same version... > > > You should have no problem as long as you're not using some feature not > supported anymore in 5.5 version this is not really true the slave should have the higher version because you can not control which statements the master writes to the binlog generally you should not mix exepct for update both starting with the slave signature.asc Description: OpenPGP digital signature
Re: Replication between different versions
2012/11/15 Lorenzo Milesi > Hi. > > Is it possible to have a master/master replication between servers of > different versions? > Now I've two 5.0.51 syncing themselves. I'm installing a new server with > 5.5.28 and I'd like if I can upgrade but still mantaining compatibility. > Otherwise I'll keep on installing the same version... You should have no problem as long as you're not using some feature not supported anymore in 5.5 version. Manuel
