Optimizing this query

Databases
1

Hi,

Can anyone point out how I should optimize this query:

SELECT 
				c.*, 
				cl.*, 
				clc.countyName as clientCounty, 
				cause.causeTitle,
				status.statusTitle
				
			FROM ".DB_PREFIX."claims c
				INNER JOIN
					".DB_PREFIX."clients cl
				ON 
					c.clientID = cl.clientID
				INNER JOIN 
					".DB_PREFIX."counties clc 
				ON 
					clc.countyID = cl.clientCountyID
				INNER JOIN 
					".DB_PREFIX."causecodes cause 
				ON
					cause.causeID = c.claimCauseCode
					
				INNER JOIN 
					".DB_PREFIX."statuscodes status 
				ON 	
					status.statusID = c.claimStatusCode
					";
				/* Drag in the specific claims */	
				
				$q .= "LEFT JOIN 
					".DB_PREFIX."liabilityclaims li 
				ON c.claimID = li.claimID
				
				LEFT JOIN 
					".DB_PREFIX."motorclaims mc 
				ON c.claimID = mc.claimID
				
				LEFT JOIN 
					".DB_PREFIX."propertyclaims pc 
				ON c.claimID = pc.claimID
					";
				/* Now join the solicitor onto the property claim and liability claims */
				
				$q .= "LEFT JOIN 
					".DB_PREFIX."solicitors solicitors
				ON (solicitors.solicitorID = li.solicitorID OR solicitors.solicitorID = pc.solicitorID)
					";
				/* Now join the loss adjuster, same as above */
				
				$q .= "LEFT JOIN 
					".DB_PREFIX."lossadjusters loss
				ON (loss.lossAdjusterID = pc.lossAdjusterID OR loss.lossAdjusterID = li.lossAdjusterID) 
					";
				/* Join the third part for motorclaims */
				
				$q .= "LEFT JOIN 
					".DB_PREFIX."thirdparties third
				ON third.thirdPartyID = mc.thirdPartyID
					
					
			WHERE 1 AND c.claimDeleted = 0

I know the problem is

LEFT JOIN 
					".DB_PREFIX."lossadjusters loss
				ON (loss.lossAdjusterID = pc.lossAdjusterID OR loss.lossAdjusterID = li.lossAdjusterID)

according the the mysql bug #17164 http://bugs.mysql.com/bug.php?id=17164

I’m just not sure how I could rearrange this.

Cheers,
Rhys

2

I’ve changed to this:


/* Now join the loss adjuster, same as above */
				
				$q .= "LEFT JOIN 
					".DB_PREFIX."lossadjusters losspc
				ON (losspc.lossAdjusterID = pc.lossAdjusterID) 
					";
				/*For optimisation purposes*/
				$q .= "LEFT JOIN 
					".DB_PREFIX."lossadjusters lossli
				ON (lossli.lossAdjusterID = li.lossAdjusterID)

Is there anything wrong with doing this?

3

That bug was from 2006 and seems to be resolved (reading the comments on that page).

What problem are you having with this query?

Did you do an EXPLAIN?

4

It was an optimisation problem.

Indexing didn’t seem to help.

5

depends on the one-to-many aspects of the repaltionship

you did not give any information in this regard, so i can’t really tell

6

What problem do this thing?

7

you mean “repaltionship” ??

yeah, that was a pretty bad typo