Query Speed

[Craig Gardner]

I have two queries that are very similar. One of the queries takes a few 
minutes (3:43:07 last run) to complete, while the other takes less than 
a second to complete.

I know these are two different queries and shouldn't take the same 
amount of time, but I based the fast query on the slower one.  I would 
like to make the slower query faster and I don't have the slightest clue 
on how to do it.  Here are my queries and their explanations if that helps:

Please let me know if there's anything that I can do.

Thanks,
Craig


Slow Query:
SELECT insco.insconame, CONCAT_WS(", ", pt.ptlname, pt.ptfname) name, pt.id,
coverage.covpatinsno, cpt.cptcode , DATE_FORMAT(proc.procdt, 
"%m-%d-%Y") procdt,
insco.inscophone, proc.id procid, payrec.payrecamt current, 
fee.expected, coverage.covinsco, proc.proccpt
FROM payrec
LEFT JOIN procrec AS proc ON payrec.payrecpatient = proc.procpatient
LEFT JOIN patient AS pt ON payrec.payrecpatient = pt.id
LEFT JOIN coverage ON coverage.covpatient = pt.id AND (proc.proccov1 = 
coverage.id OR proc.proccurcovid = coverage.id)
LEFT JOIN insco ON coverage.covinsco = insco.id
LEFT JOIN cpt ON proc.proccpt = cpt.id
LEFT JOIN feeProfiles AS fee ON fee.cpt_ID = proc.proccpt AND 
fee.insurance_ID = coverage.covinsco
WHERE payrec.payreccat = 5 AND coverage.covinsco != ""
GROUP BY insconame, name, cptcode, procdt
ORDER BY insconame

Slow Query Explanation:
+--++---+-+-++--+--+
| table| type   | possible_keys | key | key_len | 
ref| rows | 
Extra|
+--++---+-+-++--+--+
| payrec   | ALL| NULL  | NULL|NULL | 
NULL   |  179 | Using where; Using temporary; 
Using filesort |
| coverage | ALL| PRIMARY   | NULL|NULL | 
NULL   |  935 | Using 
where  |
| proc | ALL| NULL  | NULL|NULL | 
NULL   |  420 | Using 
where  |
| pt   | eq_ref | PRIMARY   | PRIMARY |   4 | 
payrec.payrecpatient   |1 | Using 
where  |
| insco| eq_ref | PRIMARY   | PRIMARY |   4 | 
coverage.covinsco  |1 
|  |
| cpt  | eq_ref | PRIMARY   | PRIMARY |   4 | 
proc.proccpt   |1 
|  |
| fee  | eq_ref | unqq  | unqq|   8 | 
proc.proccpt,coverage.covinsco |1 
|  |
+--++---+-+-++--+--+

Fast Query:
SELECT insco.insconame, CONCAT_WS(", ", pt.ptlname, pt.ptfname) name, pt.id,
coverage.covpatinsno, cpt.cptcode , DATE_FORMAT(proc.procdt, 
"%m-%d-%Y") procdt,
pt.pthphone, proc.id procid, payrec.payrecamt current, fee.expected, 
coverage.covinsco, proc.proccpt
FROM payrec   
LEFT JOIN procrec AS proc ON payrec.payrecpatient = 
proc.procpatient   LEFT JOIN patient 
AS pt ON payrec.payrecpatient = pt.id
LEFT JOIN coverage ON coverage.covpatient = pt.id AND (proc.proccov1 = 
coverage.id OR proc.proccurcovid = coverage.id)   
LEFT JOIN insco ON coverage.covinsco = insco.id
LEFT JOIN cpt ON proc.proccpt = cpt.id
LEFT JOIN feeProfiles AS fee ON fee.cpt_ID = proc.proccpt AND 
fee.insurance_ID = coverage.covinsco
WHERE payrec.payreccat = 6 AND payrec.payrecsource = 0
GROUP BY name, cptcode, procdt
ORDER BY name, insconame

Explanaion:
+--++---+-+-++--+--+
| table| type   | possible_keys | key | key_len | 
ref| rows | 
Extra|
+--++---+-+-++--+--+
| payrec   | ALL| NULL  | NULL|NULL | 
NULL   |  179 | Using where; Using temporary; 
Using filesort |
| proc | ALL| NULL  | NULL|NULL | 
NULL   |  420 
|  |
| pt   | eq_ref | PRIMARY   | PRIMARY |   4 | 
payrec.payrecpatient   |1 
|  |
| coverage | ALL| PRIMARY   | NULL|NULL | 
NULL   |  935 
|  |
| insco| eq_ref | PRIMARY   | PRIMARY |   4 | 
coverage.covinsco

Query Problem

[Craig Gardner]

I'm having trouble with a query that is supposed to grab a field from each of my 
tables. and set up a Total line at the bottom of each set.

I have:

Insurance Company table (insco)
Patient table (patient)
Procedure table (procrec)
Charges table (feeProfiles)
Patient Coverage table (coverage)
Procedure Code table (cpt)

I need to get:
Insurance company name (insco.insconame)
Patient ID & full name built from first and last fields (patient.id, 
CONCAT(patient.ptlname, ", ", patient.ptfname) )
Procedure date (procrec.procdt)
Procedure Codes (cpt.id)
Expected payment total for each procedure and a total for each insurance company ( 
SUM(feeProfile.expected) )


Currently, I get all of the fields that I need, but some of the totals at the end 
either don't show up or they are not correct.
Here's my current query:

(SELECT insco.insconame, pt.id, CONCAT_WS(", ", pt.ptlname, pt.ptfname) name,
 cpt.cptcode , DATE_FORMAT(proc.procdt, "%m-%d-%Y") procdt, FORMAT(SUM(fee.expected), 
2) sum_expected
 FROM procrec AS proc, patient AS pt, cpt, feeProfiles AS fee, coverage, insco
 WHERE proc.procpatient = pt.id AND cpt.id = proc.proccpt AND cpt.id = fee.cpt_ID 
 AND coverage.covpatient = pt.id AND coverage.covinsco = insco.id
 GROUP BY insco.insconame, CONCAT_WS(", ", pt.ptlname, pt.ptfname), 
DATE_FORMAT(proc.procdt, "%m-%d-%Y"))
UNION
(SELECT DISTINCT insco.insconame, NULL id, NULL name, NULL cptcode,
 NULL procdt, FORMAT(SUM(fee.expected), 2) sum_expected
 FROM feeProfiles AS fee, cpt , procrec, patient, coverage, insco
 WHERE procrec.procpatient=patient.id AND cpt.id = fee.cpt_ID AND procrec.proccpt = 
cpt.id
 AND coverage.id = procrec.proccurcovid AND coverage.covinsco = insco.id GROUP BY 
insco.insconame)
ORDER BY insco.insconame

Is there an easier way to do this on mysql version 4.18 or should I rework my entire 
query?

Thanks,
Craig

Totals

[Craig Gardner]

I need to get totals for the data in my database.  I stumbled upon the 
"WITH ROLLUP" modifier 
(http://www.mysql.com/doc/en/GROUP-BY-Modifiers.html) and it appears to 
be exactly what I'm looking for, except for the fact that when I try to 
use it I get the following message:

	This version of MySQL doesn't yet support 'ROLLUP'

The version of mySQL I have installed on this machine is 
"4.0.18-standard".  Is there something I need to install/configure to 
get this modifier to work properly, or is there another way to get the 
totals I want.

Thanks,
Craig
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]