[thelist] MySQL optimization

Matt Warden mwarden at gmail.com
Tue Sep 27 13:50:55 CDT 2005


-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Steve Lewis wrote:
> Anthony Ettinger wrote:
> 
>>> Query:
>>>  select DISTINCT sectionid
>>>  from SectionMembers LEFT JOIN Sections ON
>>>      Sections.id=SectionMembers.sectionid
>>>  where Sections.instid = '%bindVal01%'
>>>
...
> Note:
> It looks to me like I don't want a left join, I want an inner join - an
> inner join should prune the result tree further before the where clause
> is applied.  That still doesn't remove my distinct clause, however.  I'm
> still looking for another optimization.

Then why are you joining with SectionMembers at all? This is why I
asked for an explanation for the left join, as that was the only
reason I could see you needing to join with SM. Try this:

select id
from Sections
where instid = '%bindVal01%';

Or, do you only want sections that have SectionMembers? In that case,
you aren't going to be able to get around needing to use distinct or
group by... unless your MySQL supports subqueries. Even then, it would
be essentially the same operation, and you probably wouldn't save much.

- --
Matt Warden
Miami University
Oxford, OH, USA
http://mattwarden.com


This email proudly and graciously contributes to entropy.
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.4 (GNU/Linux)

iD8DBQFDOZSPAQ0d4HGyPE8RAqvPAJ4+OGo6VTJNNARxwV3oeVavRcY5dQCfYS+f
XucFyJZw1luOeFWJr5qVwq0=
=XWRJ
-----END PGP SIGNATURE-----


More information about the thelist mailing list