[vox-tech] SQL selecting distinct from multiple index tables (solution)

[Ted Deppner]

On Fri, Jan 30, 2004 at 10:04:35PM -0800, David Siedband wrote:
> ahh, I just got this to work.  Here's the query I used (MySQL 3.x)
> 
> select distinct Organizations.OID , Organizations.Name , 
> Organizations.Acronym
> from OrgDocs , OrgProjects , Organizations
> where (OrgDocs.OrgID = Organizations.OID) or (OrgProjects.OrgID = 
> Organizations.OID)
> 
> still interested in other ways of doing this though....

If you haven't already, try prepending an "explain" on the front of that
select query to see how expensive it's going to be.  You can then
examine with useful data the expense of various methods.  Sometimes two
queries are faster than one query (ie build an index table, then do a
simple select rather than a single complex select).

You might also try the query with some precalculated index tables, ie:
create temporary table tmp select distinct OID from OrgDocs;
replace into tmp select distinct OID from OrgProjects;
// with mysql 4.x you could use UNION and do one
// create...select...union...select
select Organizations.OID, Name, Acronym from Organizations, tmp where
(tmp.OrgID = Organizations.OID);

Depending on your table sizes a 'alter table tmp add key (OID)' might
help, or alternately using a left join may be faster (not sure on this
though):

select Organizations.OID, Name, Acronym from tmp left join Organizations
using (OID);

This presumes that OIDs in OrgDocs and OrgProjects always have matching
keys in Organizations.

-- 
Ted Deppner
http://www.deppner.us/