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 | 1 | |
| cpt | eq_ref | PRIMARY | PRIMARY | 4 | proc.proccpt | 1 | |
| fee | eq_ref | unqq | unqq | 8 | proc.proccpt,coverage.covinsco | 1 | |
+----------+--------+---------------+---------+---------+--------------------------------+------+----------------------------------------------+
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
