l i n u x - u s e r s - g r o u p - o f - d a v i s
L U G O D
 
Next Meeting:
December 2: Social gathering
Next Installfest:
TBD
Latest News:
Nov. 18: Club officer elections
Page last updated:
2004 Jan 31 13:20

The following is an archive of a post made to our 'vox-tech mailing list' by one of its subscribers.

Report this post as spam:

(Enter your email address)
Re: [vox-tech] SQL selecting distinct from multiple index tables (solution)
[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

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



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/
_______________________________________________
vox-tech mailing list
vox-tech@lists.lugod.org
http://lists.lugod.org/mailman/listinfo/vox-tech



LinkedIn
LUGOD Group on LinkedIn
Sign up for LUGOD event announcements
Your email address:
facebook
LUGOD Group on Facebook
'Like' LUGOD on Facebook:

Hosting provided by:
Sunset Systems
Sunset Systems offers preconfigured Linux systems, remote system administration and custom software development.

LUGOD: Linux Users' Group of Davis
PO Box 2082, Davis, CA 95617
Contact Us

LUGOD is a 501(c)7 non-profit organization
based in Davis, California
and serving the Sacramento area.
"Linux" is a trademark of Linus Torvalds.

Sponsored in part by:
Appahost Applications
For a significant contribution towards our projector, and a generous donation to allow us to continue meeting at the Davis Library.