[thelist] MySQL optimization
Matt Warden
mwarden at gmail.com
Tue Sep 27 13:23:25 CDT 2005
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Steve Lewis wrote:
> Query:
> select DISTINCT sectionid
> from SectionMembers LEFT JOIN Sections ON
> Sections.id=SectionMembers.sectionid
> where Sections.instid = '%bindVal01%'
>
> SectionMembers is an association table joining groups (sections) to
> users (members). The SectionMembers table has more than 100k rows.
>
> Does anyone see any way to remove the DISTINCT or identify other
> optimizations for this query?
Can you explain why you are using a left join here?
Also, make sure you have an index on SectionMembers.sectionid
An index on instid will help you too, unless you really meant to put
"Sections.instid like '%bindVal01%'"
{Insert normal caveats about considering your select/update ratio when
adding an index.}
- --
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)
iD8DBQFDOY4dAQ0d4HGyPE8RAofsAJ0XHygg9zloGzJvxXhj9VOEP9p2HwCfWSxi
ydLh3ABtRdMHjFYK1KT1XN0=
=AmfM
-----END PGP SIGNATURE-----
More information about the thelist
mailing list