MySQL Forums
Forum List  »  InnoDB

Re: Innodb bogs down on complicated query(ies)
Posted by: Lee Krawczyk
Date: May 04, 2006 12:53PM

Thanks Marko. If I can't get this resolved this way we probably will get the support contract.

The main InnoDB table hierarchy is : cases -> cases_benefits -> cases_diagnoses -> cases_providers -> cases_procedures.

All other tables are support tables for codes contained in the main tables, which are all MyISAM.

All tables must be left joined so as to expose all high level data regardless of whether the lower levels or support items exist.

Here's the query and the output from the explain:

mysql> explain extended select c1.case_id,
-> p2.social_sec_no,
-> concat_ws(' ',p1.appellation,p1.first,p1.middle,p1.last,p1.designation),
-> p1.last,
-> c1.street,
-> c1.addr2,
-> trim(both ',' from concat_ws(' ',concat_ws(', ',c1.city,c1.state),c1.zip)),
-> p1.plan1_type,
-> pr.name,
-> trim(both ',' from concat_ws(' ',concat_ws(', ',cp1.city,cp1.state),cp1.zip)),
-> fa.name,
-> trim(both ',' from concat_ws(' ',concat_ws(', ',cb1.city,cb1.state),cb1.zip)),
-> cb1.benefit_code,
-> cb1.retro,
-> cb1.approved,
-> date_format(cb1.admit_date,'%c-%d-%Y'),
-> date_format(cb1.dischrg_date,'%c-%d-%Y'),
-> cpr1.service_date,
-> cd1.result_code,
-> cd1.diag_icd,
-> i1.description,
-> cpr1.proc_icd,
-> i2.description,
-> cpr1.proc_cpt,
-> c.long_desc,
-> cd1.reason,
-> datediff(cb1.dischrg_date,cb1.admit_date),
-> b.description,
-> cp1.provider_id,
-> date_format(cpr1.service_date,'%c-%d-%Y'),
-> cb1.open,
-> p1.first
-> from cases c1 left join cases_benefits cb1 using (case_id)
-> left join benefits b using (benefit_code)
-> left join facilities fa using (facility_id)
-> left join cases_diagnoses cd1 using (case_id,benefit_code)
-> left join cases_providers cp1 using (case_id,benefit_code,diag_icd)
-> left join providers pr using (provider_id)
-> left join cases_procedures cpr1 using (case_id,benefit_code,diag_icd,provider_id)
-> left join participants p1 using (participant_id)
-> left join participants p2 on c1.insured_id = p2.participant_id
-> left join claimsmaster.icds i1 on cd1.diag_icd = i1.icd_code
-> left join claimsmaster.icds i2 on cpr1.proc_icd = i2.icd_code
-> left join claimsmaster.cpts_hcpcs c on cpr1.cpt_id = c.cpt_id
-> where c1.plan_id = 70098
-> and (cpr1.service_date between '2006-01-01' and '2006-01-31'
-> or cb1.open between '2006-01-01' and '2006-01-31'
-> or cb1.closed between '2006-01-01' and '2006-01-31'
-> or cb1.admit_date between '2006-01-01' and '2006-01-31'
-> or cb1.dischrg_date between '2006-01-01' and '2006-01-31'
-> )
-> order by p1.last, p1.first, c1.case_id, cb1.open, cb1.benefit_code, cd1.result_code desc, cpr1.service_date desc, cpr1.proc_icd;

+----+-------------+-------+--------+---------------+---------+---------+----------------------------------------------------------------------------------------------------------+--------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+---------------+---------+---------+----------------------------------------------------------------------------------------------------------+--------+----------------------------------------------+
| 1 | SIMPLE | c1 | ALL | NULL | NULL | NULL | NULL | 201903 | Using where; Using temporary; Using filesort |
| 1 | SIMPLE | cb1 | ref | PRIMARY | PRIMARY | 4 | utilization.c1.case_id | 1 | |
| 1 | SIMPLE | b | ref | PRIMARY | PRIMARY | 12 | utilization.cb1.benefit_code | 1 | |
| 1 | SIMPLE | fa | eq_ref | PRIMARY | PRIMARY | 4 | utilization.cb1.facility_id | 1 | |
| 1 | SIMPLE | cd1 | ref | PRIMARY | PRIMARY | 16 | utilization.c1.case_id,utilization.cb1.benefit_code | 1 | |
| 1 | SIMPLE | cp1 | ref | PRIMARY | PRIMARY | 28 | utilization.c1.case_id,utilization.cb1.benefit_code,utilization.cd1.diag_icd | 1 | |
| 1 | SIMPLE | pr | eq_ref | PRIMARY | PRIMARY | 4 | utilization.cp1.provider_id | 1 | |
| 1 | SIMPLE | cpr1 | ref | PRIMARY | PRIMARY | 32 | utilization.c1.case_id,utilization.cb1.benefit_code,utilization.cd1.diag_icd,utilization.cp1.provider_id | 1 | Using where |
| 1 | SIMPLE | p1 | eq_ref | PRIMARY | PRIMARY | 8 | utilization.c1.participant_id | 1 | |
| 1 | SIMPLE | p2 | eq_ref | PRIMARY | PRIMARY | 8 | utilization.c1.insured_id | 1 | |
| 1 | SIMPLE | i1 | ref | PRIMARY | PRIMARY | 12 | utilization.cd1.diag_icd | 1 | |
| 1 | SIMPLE | i2 | ref | PRIMARY | PRIMARY | 12 | utilization.cpr1.proc_icd | 1 | |
| 1 | SIMPLE | c | eq_ref | PRIMARY | PRIMARY | 4 | utilization.cpr1.cpt_id | 1 | |
+----+-------------+-------+--------+---------------+---------+---------+----------------------------------------------------------------------------------------------------------+--------+----------------------------------------------+
13 rows in set, 1 warning (0.05 sec)


| Level | Code | Message ------------------------------------------------------+
| Note | 1003 | select `utilization`.`c1`.`case_id` AS `case_id`,`utilization`.`p2`.`social_sec_no` AS `social_sec_no`,concat_ws(_latin1' ',`utilization`.`p1`.`appellation`,`utilization`.`p1`.`first`,`utilization`.`p1`.`middle`,`utilization`.`p1`.`last`,`utilization`.`p1`.`designation`) AS `concat_ws(' ',p1.appellation,p1.first,p1.middle,p1.last,p1.designation)`,`utilization`.`p1`.`last` AS `last`,`utilization`.`c1`.`street` AS `street`,`utiliza
tion`.`c1`.`addr2` AS `addr2`,trim(concat_ws(_latin1' ',concat_ws(_latin1', ',`utilization`.`c1`.`city`,`utilization`.`c1`.`state`),`utilization`.`c1`.`zip`),_latin1',') AS `trim(both ',' from concat_ws(' ',concat_ws(', ',c1.city,c1.state),c1.zip))`,`utilization`.`p1`.`plan1_type` AS `plan1_type`,`utilization`.`pr`.`name` AS `name`,trim(concat_ws(_latin1' ',concat_ws(_latin1', ',`utilization`.`cp1`.`city`,`utilization`.`cp1`.`state`),`utilization
`.`cp1`.`zip`),_latin1',') AS `trim(both ',' from concat_ws(' ',concat_ws(', ',cp1.city,cp1.state),cp1.zip))`,`utilization`.`fa`.`name` AS `name`,trim(concat_ws(_latin1' ',concat_ws(_latin1', ',`utilization`.`cb1`.`city`,`utilization`.`cb1`.`state`),`utilization`.`cb1`.`zip`),_latin1',') AS `trim(both ',' from concat_ws(' ',concat_ws(', ',cb1.city,cb1.state),cb1.zip))`,`utilization`.`cb1`.`benefit_code` AS `benefit_code`,`utilization`.`cb1`.`retr
o` AS `retro`,`utilization`.`cb1`.`approved` AS `approved`,date_format(`utilization`.`cb1`.`admit_date`,_latin1'%c-%d-%Y') AS `date_format(cb1.admit_date,'%c-%d-%Y')`,date_format(`utilization`.`cb1`.`dischrg_date`,_latin1'%c-%d-%Y') AS `date_format(cb1.dischrg_date,'%c-%d-%Y')`,`utilization`.`cpr1`.`service_date` AS `service_date`,`utilization`.`cd1`.`result_code` AS `result_code`,`utilization`.`cd1`.`diag_icd` AS `diag_icd`,`claimsmaster`.`i1`.`
description` AS `description`,`utilization`.`cpr1`.`proc_icd` AS `proc_icd`,`claimsmaster`.`i2`.`description` AS `description`,`utilization`.`cpr1`.`proc_cpt` AS `proc_cpt`,`claimsmaster`.`c`.`long_desc` AS `long_desc`,`utilization`.`cd1`.`reason` AS `reason`,(to_days(`utilization`.`cb1`.`dischrg_date`) - to_days(`utilization`.`cb1`.`admit_date`)) AS `datediff(cb1.dischrg_date,cb1.admit_date)`,`utilization`.`b`.`description` AS `description`,`uti
lization`.`cp1`.`provider_id` AS `provider_id`,date_format(`utilization`.`cpr1`.`service_date`,_latin1'%c-%d-%Y') AS `date_format(cpr1.service_date,'%c-%d-%Y')`,`utilization`.`cb1`.`open` AS `open`,`utilization`.`p1`.`first` AS `first` from `utilization`.`cases` `c1` left join `utilization`.`cases_benefits` `cb1` on((`utilization`.`cb1`.`case_id` = `utilization`.`c1`.`case_id`)) left join `utilization`.`benefits` `b` on((`utilization`.`b`.`benefi
t_code` = `utilization`.`cb1`.`benefit_code`)) left join `utilization`.`facilities` `fa` on((`utilization`.`fa`.`facility_id` = `utilization`.`cb1`.`facility_id`)) left join `utilization`.`cases_diagnoses` `cd1` on(((`utilization`.`cd1`.`case_id` = `utilization`.`c1`.`case_id`) and (`utilization`.`cd1`.`benefit_code` = `utilization`.`cb1`.`benefit_code`))) left join `utilization`.`cases_providers` `cp1` on(((`utilization`.`cp1`.`diag_icd` = `util
ization`.`cd1`.`diag_icd`) and (`utilization`.`cp1`.`case_id` = `utilization`.`c1`.`case_id`) and (`utilization`.`cp1`.`benefit_code` = `utilization`.`cb1`.`benefit_code`))) left join `utilization`.`providers` `pr` on((`utilization`.`pr`.`provider_id` = `utilization`.`cp1`.`provider_id`)) left join `utilization`.`cases_procedures` `cpr1` on(((`utilization`.`cpr1`.`diag_icd` = `utilization`.`cd1`.`diag_icd`) and (`utilization`.`cpr1`.`case_id` = `
utilization`.`c1`.`case_id`) and (`utilization`.`cpr1`.`benefit_code` = `utilization`.`cb1`.`benefit_code`) and (`utilization`.`cpr1`.`provider_id` = `utilization`.`cp1`.`provider_id`))) left join `utilization`.`participants` `p1` on((`utilization`.`p1`.`participant_id` = `utilization`.`c1`.`participant_id`)) left join `utilization`.`participants` `p2` on((`utilization`.`p2`.`participant_id` = `utilization`.`c1`.`insured_id`)) left join `claimsma
ster`.`icds` `i1` on((`claimsmaster`.`i1`.`icd_code` = `utilization`.`cd1`.`diag_icd`)) left join `claimsmaster`.`icds` `i2` on((`claimsmaster`.`i2`.`icd_code` = `utilization`.`cpr1`.`proc_icd`)) left join `claimsmaster`.`cpts_hcpcs` `c` on((`claimsmaster`.`c`.`cpt_id` = `utilization`.`cpr1`.`cpt_id`)) where ((`utilization`.`c1`.`plan_id` = 70098) and ((`utilization`.`cpr1`.`service_date` between 20060101 and 20060131) or (`utilization`.`cb1`.`op
en` between 20060101 and 20060131) or (`utilization`.`cb1`.`closed` between 20060101 and 20060131) or (`utilization`.`cb1`.`admit_date` between 20060101 and 20060131) or (`utilization`.`cb1`.`dischrg_date` between 20060101 and 20060131))) order by `utilization`.`p1`.`last`,`utilization`.`p1`.`first`,`utilization`.`c1`.`case_id`,`utilization`.`cb1`.`open`,`utilization`.`cb1`.`benefit_code`,`utilization`.`cd1`.`result_code` desc,`utilization`.`cpr1
`.`service_date` desc,`utilization`.`cpr1`.`proc_icd` |

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Innodb bogs down on complicated query(ies)
1843
May 04, 2006 12:53PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.