Margaret MacDonald <[EMAIL PROTECTED]> wrote on 09/07/2004 12:07:54:
> Is there a generally-accepted rule of thumb for estimating the
> performance cost of joins? I can't find one even in Date, but
> intuitively it seems as though there must be one by now.
I don't think there is a general answer to this any more than there is to
the general question of optimising selects. It all depends upon your
indexes, and whether the optimiser notices them. I have had an order of
magnitude speedup on a join by introducing an appropriate index. This says
that is no underlaying "cost of a join", only the cost of a particular
join.
There are numerous tips available for optimising MySQL selects, which
should followed carefully. However, I have found that, when you get to the
bottom line, the only way to handle it is to dry-run it on paper. Say "If
I had to do this search on paper tables, what indexes would I want?". If
you then create those indexes, MySQL is pretty good at spotting the
appropriate ones and using them.
One strategy that seems useful is that if you are doing a join of A and B,
and A is primarily a selector table with the bulk data in B, which is what
your application sounds like, you build an index which contains all the
fields in A you want for a query. Thus if you want to do
Select A1, A2, B.* FROM A JOIN B ON A1 = B1 WHERE ...
Select A1, A3, C.* FROM A JOIN C ON A1 = C1 WHERE ...
you build indexes on both (A1, A2) and (A1, A3). This allows MySQL to
extract all the necessary information from the indexes without reading the
underlying records.
Alec
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
