Optimizing join queries with indexes.

[Guest]

Select *
from table1 as a inner join table2 as b where a.field3 = b.field3 and
a.field2 = b.field2 where a.field1 = 803;

Table1 already has a compound index as field1, field2, field3 (in that
order). What can I do to optimize the above query. Would it help to add a
compound index on table2.field2, table2.field3? There is no table2.field1.

Also, would the order of the join conditions matter? Should field2 be
compared before field3?

 

[MGFoster]

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

The complex index (field1, field2, field3) will work well for that
particular query.

Generally, in a multiple AND comparison you put the comparison that will
FAIL first 'cuz then the other comparisons will not be made, thus saving
time by eliminating the unnecessary, further, evaluations.

IOW, in your example if there are alot of records in table A that have
field3 equal to "xxx", but only one record with field3 equal to "yyy",
and, in table B there is only one field3 equal to "yyy," then you know
that a comparison of both table's field3s will succeed only ONE time.
Therefore this comparison is done first, 'cuz you know that most of the
time this comparison will fail.

For more info see the book: _SQL Performance Tuning_, by Peter Gulutzan
& Trudy Pelzer. Publisher: Addison-Wesley.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQYLQUYechKqOuFEgEQKGigCeOj+TsJjrFC3kl4oNPIIjLAEyd3gAoIJd
kff7CBbSX7r29+MCgNsE/eO2
=qIVT
-----END PGP SIGNATURE-----