MySQL Forums
Forum List  »  InnoDB

Re: Innodb bogs down on complicated query(ies)
Posted by: Steve Johnson
Date: May 09, 2006 08:28PM

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

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Innodb bogs down on complicated query(ies)
1966
May 09, 2006 08:28PM


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.