List:General Discussion« Previous MessageNext Message »
From:Alec.Cawley Date:July 9 2004 12:28pm
Subject:Re: Cost of joins?
View as plain text  
Margaret MacDonald <scratch65536@stripped> 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

Thread
Cost of joins?Margaret MacDonald9 Jul
  • Re: Cost of joins?Jigal van Hemert9 Jul
  • Re: Cost of joins?Martijn Tonies9 Jul
    • Re: Cost of joins?Alec.Cawley9 Jul
    • RE: Cost of joins?Lachlan Mulcahy12 Jul
      • Re: Cost of joins?Martijn Tonies12 Jul
      • Re: query gets count wrongGerald Taylor12 Jul
  • Re: Cost of joins?Alec.Cawley9 Jul
  • Re: Cost of joins?Jigal van Hemert9 Jul
    • Re: Cost of joins?Jochem van Dieten9 Jul
  • Re: Cost of joins?Martijn Tonies9 Jul
    • Re: Cost of joins?Alec.Cawley9 Jul
      • Re: Cost of joins?Jochem van Dieten9 Jul
    • Re: Cost of joins?(Michael Johnson)9 Jul
  • Re: Cost of joins?Jigal van Hemert9 Jul
  • Re: Cost of joins?Martijn Tonies9 Jul
RE: Cost of joins?Bob)9 Jul
Re: Cost of joins?Martijn Tonies9 Jul
Re: Cost of joins?Martijn Tonies9 Jul
Re: query gets count wrongSGreen12 Jul