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, `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
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
query tuning
I have a query I'm having trouble with. If do this query which is DATE plus ProcModecode, it is very fast: SELECT e.zzk FROM exams e -- JOIN Appt_ a ON e.IdAppt = a.IdAppt -- JOIN Affil_ af ON a.IdAffil_primary = af.IdAffil WHERE e.dateexam = '2012-09-01' AND e.dateexam = '2012-09-30' AND e.ProcModeCode = 'P' Notice that the joins are commented out. If I do this query, which doesn't have the ProcModeCode, but does have the IdPract across the join, it is also fast, but not as fast: SELECT e.zzk FROM exams e JOIN Appt_ a ON e.IdAppt = a.IdAppt JOIN Affil_ af ON a.IdAffil_primary = af.IdAffil WHERE e.dateexam = '2012-09-01' AND e.dateexam = '2012-09-30' AND af.IdPract = 'D00400' BUT, if I do this, with the ProcModeCode AND the IdPract, it is so slow I have to kill the query: SELECT e.zzk FROM exams e JOIN Appt_ a ON e.IdAppt = a.IdAppt JOIN Affil_ af ON a.IdAffil_primary = af.IdAffil WHERE e.dateexam = '2012-09-01' AND e.dateexam = '2012-09-30' AND af.IdPract = 'D00400' AND e.ProcModeCode = 'L' ORDER BY e.zzk DESC LIMIT 30 Here is the result of an explain on this: id select_type table typepossible_keys key key_len ref rowsExtra 1 SIMPLE e index dateexam,IdAppt,ProcModeCodePRIMARY 4 NULL1121043 Using where 1 SIMPLE af ref IdAffil,IdPract IdPract 51 const 1 Using where 1 SIMPLE a ref IdAppt IdAppt 99 RIS_archive.e.IdAppt 1 Using where Any ideas about how I can fix this? TIA Jim McNeely -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
RE: query tuning
Can't help without the SHOW CREATE TABLEs. Perhaps e needs one of these: INDEX(zzk) INDEX(ProcModeCode, dateexam) -- in that order (I can't predict which index it would use.) Are IdAppt the same datatype and collation in each table? -Original Message- From: James W. McNeely [mailto:jmcne...@nwrads.com] Sent: Wednesday, November 14, 2012 2:34 PM To: mysql@lists.mysql.com Subject: query tuning I have a query I'm having trouble with. If do this query which is DATE plus ProcModecode, it is very fast: SELECT e.zzk FROM exams e -- JOIN Appt_ a ON e.IdAppt = a.IdAppt -- JOIN Affil_ af ON a.IdAffil_primary = af.IdAffil WHERE e.dateexam = '2012-09-01' AND e.dateexam = '2012-09-30' AND e.ProcModeCode = 'P' Notice that the joins are commented out. If I do this query, which doesn't have the ProcModeCode, but does have the IdPract across the join, it is also fast, but not as fast: SELECT e.zzk FROM exams e JOIN Appt_ a ON e.IdAppt = a.IdAppt JOIN Affil_ af ON a.IdAffil_primary = af.IdAffil WHERE e.dateexam = '2012- 09-01' AND e.dateexam = '2012-09-30' AND af.IdPract = 'D00400' BUT, if I do this, with the ProcModeCode AND the IdPract, it is so slow I have to kill the query: SELECT e.zzk FROM exams e JOIN Appt_ a ON e.IdAppt = a.IdAppt JOIN Affil_ af ON a.IdAffil_primary = af.IdAffil WHERE e.dateexam = '2012- 09-01' AND e.dateexam = '2012-09-30' AND af.IdPract = 'D00400' AND e.ProcModeCode = 'L' ORDER BY e.zzk DESC LIMIT 30 Here is the result of an explain on this: idselect_type table typepossible_keys key key_len ref rowsExtra 1 SIMPLE e index dateexam,IdAppt,ProcModeCodePRIMARY 4 NULL1121043 Using where 1 SIMPLE af ref IdAffil,IdPract IdPract 51 const 1 Using where 1 SIMPLE a ref IdAppt IdAppt 99 RIS_archive.e.IdAppt 1 Using where Any ideas about how I can fix this? TIA Jim McNeely -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: query tuning
` text, `Q10_answer` text, `Q10_title` text, `Q11_answer` text, `Q11_title` text, `Q12_answer` text, `Q12_title` text, `Q13_answer` text, `Q13_title` text, `Q14_answer` text, `Q14_title` text, `Q15_answer` text, `Q15_title` text, `Q16_answer` text, `Q16_title` text, `Q17_answer` text, `Q17_title` text, `Q18_answer` text, `Q18_title` text, `Q19_answer` text, `Q19_title` text, `Q20_answer` text, `Q20_title` text, `ApptQContrstAllrgy` text, `export` varchar(11) DEFAULT NULL, `Q21_answer` text, `Q22_answer` text, `Q23_answer` text, `Q24_answer` text, `Q25_answer` text, `Q26_answer` text, `Q27_answer` text, `Q28_answer` text, `Q29_answer` text, `Q30_answer` text, `Q21_title` text, `Q22_title` text, `Q23_title` text, `Q24_title` text, `Q25_title` text, `Q26_title` text, `Q27_title` text, `Q28_title` text, `Q29_title` text, `Q30_title` text, `statusnumber` int(11) DEFAULT NULL, `ranking` int(11) DEFAULT NULL, `no_show_letter_status` varchar(45) DEFAULT NULL, `id_sci_24` varchar(45) DEFAULT NULL, `IdAffil_primary` varchar(45) DEFAULT NULL, PRIMARY KEY (`zzk`), KEY `zzk` (`zzk`), KEY `zModTimestamp` (`zModTimestamp`), KEY `IdPatient` (`IdPatient`), KEY `IdAppt` (`IdAppt`), KEY `apptdate` (`ApptDate`), KEY `ApptTimeOut` (`ApptTimeOut`), KEY `IdPriCarePhy` (`IdPriCarePhy`), KEY `dont_import` (`not_appt`), KEY `IdArea` (`IdArea`), KEY `statusnumber` (`statusnumber`), KEY `no_show_letter` (`no_show_letter_status`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='InnoDB free: 4068352 kB' CREATE TABLE `Affil_` ( `IdAffiliation` varchar(16) DEFAULT NULL, `IdPract` varchar(16) DEFAULT NULL, `Backline` varchar(16) DEFAULT NULL, `BacklineAC` varchar(8) DEFAULT NULL, `NWRSpeedDial` varchar(32) DEFAULT NULL, `HospExt` varchar(16) DEFAULT NULL, `NWMBCon` varchar(32) DEFAULT NULL, `Email` varchar(128) DEFAULT NULL, `PhoneAC` varchar(8) DEFAULT NULL, `Phone` varchar(16) DEFAULT NULL, `FaxAC` varchar(8) DEFAULT NULL, `Fax` varchar(16) DEFAULT NULL, `NWR#` varchar(16) DEFAULT NULL, `PractName` varchar(64) DEFAULT NULL, `IdAffil` varchar(30) DEFAULT NULL, `NameLast` varchar(32) DEFAULT NULL, `NameFirst` varchar(32) DEFAULT NULL, `IdAffilImport` varchar(64) DEFAULT NULL, `IsInactiveX` varchar(8) DEFAULT NULL, `AddrSuite` varchar(128) DEFAULT NULL, `OfficeName` varchar(255) DEFAULT NULL, `short_name` varchar(255) DEFAULT NULL, `NameLastXFirst` varchar(255) DEFAULT NULL, `LocationCode` varchar(64) DEFAULT NULL, `PagerAC` varchar(8) DEFAULT NULL, `Pager` varchar(16) DEFAULT NULL, `AnsSrvcAC` varchar(8) DEFAULT NULL, `AnsSrvc` varchar(16) DEFAULT NULL, `MCAffil` varchar(32) DEFAULT NULL, `ReportPreference` varchar(32) DEFAULT NULL, `ReportPrinter` varchar(32) DEFAULT NULL, `ReportViaPACS` varchar(255) DEFAULT NULL, `DupeMark` varchar(8) DEFAULT NULL, `SJHPractName` varchar(128) DEFAULT NULL, `NWR#Inactive` varchar(128) DEFAULT NULL, `zModDate` date DEFAULT NULL, `zModTime` time DEFAULT NULL, `zModTimestamp` datetime DEFAULT NULL, `IdLastword` varchar(16) DEFAULT NULL, `zzk` int(11) NOT NULL, `is_deleted` int(11) DEFAULT NULL, `mysql_timestamp` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, `contact_classification` varchar(45) DEFAULT NULL, PRIMARY KEY (`zzk`), KEY `zzk` (`zzk`), KEY `zModTimestamp` (`zModTimestamp`), KEY `IdAffil` (`IdAffil`), KEY `mysql_timestamp` (`mysql_timestamp`), KEY `IdPract` (`IdPract`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 On Nov 14, 2012, at 3:26 PM, Rick James wrote: Can't help without the SHOW CREATE TABLEs. Perhaps e needs one of these: INDEX(zzk) INDEX(ProcModeCode, dateexam) -- in that order (I can't predict which index it would use.) Are IdAppt the same datatype and collation in each table? -Original Message- From: James W. McNeely [mailto:jmcne...@nwrads.com] Sent: Wednesday, November 14, 2012 2:34 PM To: mysql@lists.mysql.com Subject: query tuning I have a query I'm having trouble with. If do this query which is DATE plus ProcModecode, it is very fast: SELECT e.zzk FROM exams e -- JOIN Appt_ a ON e.IdAppt = a.IdAppt -- JOIN Affil_ af ON a.IdAffil_primary = af.IdAffil WHERE e.dateexam = '2012-09-01' AND e.dateexam = '2012-09-30' AND e.ProcModeCode = 'P' Notice that the joins are commented out. If I do this query, which doesn't have the ProcModeCode, but does have the IdPract across the join, it is also fast, but not as fast: SELECT e.zzk FROM exams e JOIN Appt_ a ON e.IdAppt = a.IdAppt JOIN Affil_ af ON a.IdAffil_primary = af.IdAffil WHERE e.dateexam = '2012- 09-01' AND e.dateexam = '2012-09-30' AND af.IdPract = 'D00400' BUT, if I do this, with the ProcModeCode AND the IdPract, it is so slow I have to kill the query: SELECT e.zzk FROM exams e JOIN Appt_ a ON e.IdAppt = a.IdAppt JOIN Affil_ af
RE: query tuning
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, `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) DEFAULT
query tuning.
Hi All, I am using mysql 5.0.41, on debian, with 8 CPU, 8 GB RAM. I am having the below query with explain plan select * from (select * from (select truncate0.34*LOG(10,((NO_BIDS + 1)/(TOTAL_QTY + 1 + (0.24*LOG(10,100*( (SOLD_QTY + 1) / (TOTAL_QTY + 1 + 0.14*(1 / LOG(10, 10 + TOTAL_QTY / NO_SLRS))) * 100),2) as CLUSTER_RANK, LEAF_CATEG_ID, CD.CLUSTER_ID, SIGNATURE, NO_LISTINGS, NO_SUCC_LISTINGS, ASP, NO_BIDS, MIN_PRICE, MAX_PRICE, NO_SLRS, TOTAL_QTY, SOLD_QTY from Cluster_data CD where CD.cluster_id in ( select B2.child_cluster_id from cluster_hierarchy B2 LEFT JOIN cluster_hierarchy B1 ON B2.child_cluster_id=B1.cluster_id where B1.cluster_id IS NULL and B2.child_cluster_id in (123781710012,123781710015,123781710023,123781710024,123781710031,123781710033,123781710035,123781710067,123781710067,123781710069)) LIMIT 500 )X order by 1 DESC ) Y where CLUSTER_RANK 29 LIMIT 30 +++---+--+-+-+-+--+---+-+ | id | select_type| table | type | possible_keys | key | key_len | ref | rows | Extra | +++---+--+-+-+-+--+---+-+ | 1 | PRIMARY| NULL | NULL | NULL| NULL| NULL| NULL | NULL | Impossible WHERE noticed after reading const tables | | 2 | DERIVED| NULL | NULL | NULL| NULL| NULL| NULL | NULL | no matching row in const table | | 3 | DERIVED| CD| ALL | NULL| NULL| NULL| NULL | 12752 | Using where | | 4 | DEPENDENT SUBQUERY | B2| ref | CH_CCI_IDX_0803120214 | CH_CCI_IDX_0803120214 | 10 | func | 3 | Using where; Using index| | 4 | DEPENDENT SUBQUERY | B1| ref | KD_KW_KI_IDX_0803120214 | KD_KW_KI_IDX_0803120214 | 10 | research.B2.child_cluster_id | 4 | Using where; Using index; Not exists| +++---+--+-+-+-+--+---+-+ mysql show index from cluster_data; +--++---+--+---+---+-+--++--++-+ | Table| Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +--++---+--+---+---+-+--++--++-+ | cluster_data | 0 | PRIMARY |1 | cluster_id| A | 12752 | NULL | NULL | | BTREE | | | cluster_data | 1 | CD_LCI_IDX_0803120214 |1 | leaf_categ_id | A | 2 | NULL | NULL | | BTREE | | +--++---+--+---+---+-+--++--++-+ Is there any options for me to tune this query. Here in the test envirionment CD table has only 12752 rows, but actually in productin it will have close to 8 Million rows. So with the above explain plan, the query would take too much time as its reading FULL TABLE on CD. Can you please help me in tuning this query. Also the IN clause will have close to 1000 values at any time.
