Re: Innodb bogs down on complicated query(ies)

[Steve Johnson]

Lee-

Have you tried reordering your WHERE clause, or creating UNIONS, or getting rid of the BETWEENs?

For reordering the WHERE clause, I would change it from this:

"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'
-> )"

to:

"where (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'
-> )
and c1.plan_id = 70098"

If that doesn't give you much performance gain, try using UNION instead of the multiple OR statements. The UNION's would be setup something like this:

SELECT [same as currently written]
FROM [same as currently written]
WHERE cb1.dischrg_date between '2006-01-01' and '2006-01-31'
AND c1.plan_id = 70098

UNION

SELECT [same as currently written]
FROM [same as currently written]
WHERE cb1.admit_date between '2006-01-01' and '2006-01-31'
AND c1.plan_id = 70098

UNION

......and so on, and so on, until you have replaced your OR statements with UNIONed SELECT queries.

One last option if you have already reordered the WHERE clause and created the UNIONed SELECT statements, get rid of BETWEEN statement, and replace it with >= and <=. I would change it from this:

WHERE cb1.dischrg_date between '2006-01-01' and '2006-01-31'
AND c1.plan_id = 70098

to this:

WHERE cb1.dischrg_date <= '2006-01-31'
AND cb1.dischrg_date >= '2006-01-01'
AND c1.plan_id = 70098

Good luck man.

--Steve