List:General Discussion« Previous MessageNext Message »
From:Matt Neimeyer Date:October 20 2009 4:46pm
Subject:VFP to MySQL Query Optimization
View as plain text  
I feel like there should be a better way to do this... So I'm hoping
someone will be able to advise.

We have contacts that belong to organizations. We also have a document
tracker. Holding over from VFP you can have up to six organization ids
on the document and up to six contact ids. Right now the select to see
if a contact has documents looks like this...

SELECT * FROM Contacts WHERE
   (id IN (SELECT contid1 FROM documents)
   OR id IN (SELECT contid2 FROM documents)
   OR id IN (SELECT contid3 FROM documents)
   OR id IN (SELECT contid4 FROM documents)
   OR id IN (SELECT contid5 FROM documents)
   OR id IN (SELECT contid6 FROM documents)
   OR orgid IN (SELECT orgid1 FROM documents)
   OR orgid IN (SELECT orgid2 FROM documents)
   OR orgid IN (SELECT orgid3 FROM documents)
   OR orgid IN (SELECT orgid4 FROM documents)
   OR orgid IN (SELECT orgid5 FROM documents)
   OR orgid IN (SELECT orgid6 FROM documents))

Which is UGLY... and I feel like there should be a better way (I know
I could break that out into a many-many relationship via a third
linking table but I'm not 'able' to do that now.)

The only change I can think of is to union the two halves of the
select but I'm not sure if that would be better... (IE id in (select
contid1 from documents union select contid2 from documents) etc)

Any advice or is this the best I'm going to get until I can reorganize
the underlying structure?

Thanks!

Matt
Thread
VFP to MySQL Query OptimizationMatt Neimeyer20 Oct