[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