List:General Discussion« Previous MessageNext Message »
From:Roger Baklund Date:June 8 2002 12:09am
Subject:Re: Maximum JOINED tables
View as plain text  
* Benjamin Pflugmann
> On Fri 2002-06-07 at 11:43:38 +0200, roger.baklund@stripped wrote:
> [...]
> > However, I think my question is valid... I don't think it is a
> good idea to
> > limit the number of joins based on the processor architecture, and this
> > makes me wonder what other 'strange' limitations I might find
> in mysql...
> > some questions to Victoria/MySQL AB:
>
> Well, of course, they could limit the join to 31 tables on all
> platforms.
>
> But then people would start to ask, why this limit is artificially low
> on 64 bit platforms.

Well... :)

I would agree to this if any 64 bit based functionality was limited to 64
bit platforms, but that is not the case. For instance, we can use BIGINT on
all platforms. I don't think 'people' would think there is a connection
between the processor word width and the number of joins in a single
select... but of course, I could be wrong.

I do not suggest that the limit should be lowered to 31 on all platforms,
but I find it hard to believe that it is impossible to implement joins in a
different way, so that the max number of joins would be the same on all
platforms. Monty & company have of course worked hard to prevent platform
dependency, and it seems as this is the only part of mysql directly
dependant on the processor architecture. I find it strange, I don't say it
is not true.

> > What (other) features of mysql are dependant on the processor
> > architecture?
>
> file size (indirectly... it is dependend on the OS, but the OS is
> often dependend on the architecture).
>
> I can not think of any other, currently.

If file size qualifies, then the table name letter casing issue and the
differences in the floating point functions also qualifies. But all of these
are OS dependant, not directly processor architecture dependant. And I think
the number of joins in a query is a _feature_ on a different level.

> [...]
> > Is this limitation "by design", and if so, why? (I suspect it is by
> > accident, and that's ok. That would mean it will probably be
> fixed in the
> > future.)
>
> I can only guess, but I think it is still there, because increasing it
> would be fruitless as long as finding the optimal join is in O(2^N).

You are probably right.

> Additionally, people seem to seldom need so many tables in a single
> join, and that's probably, too, why the join order optimizer is still
> the way it is.

Yes... and seldom = sometimes... :)

I don't know the limits for any of the other rdbms, but crashme reports +64
for IBM, Microsoft, Oracle and PostgreSQL.

> > The join order optimization is done before the query is executed, so
> > I guess you are not talking about the _query_ beeing to slow, but
> > the _preparation_ of the query... right?
>
> I am not sure what your point is.

Just making sure we are talking about the same thing. :)

> The client will have to wait too long, no matter where the time is spent.

The nature of my application allows for a relatively slow query preparation,
because I have few simultaneous queries. I guess up to 0.2 sec could have
been acceptable. (I want the answer within 0.5 sec.)

Query execution time will always increase for each join you add to a select,
but that was of course _not_ what you was talking about. I should have known
better. :)

> > In our current implementation of this system, we have about 20
> tables in the
> > main select statement, and we have never seen this problem. Will it be a
> > problem with 24 tables? 28 tables? You probably don't know the answer to
> > these questions, but maybe you can tell me more about why you suspect 30
> > tables would be too slow...?
>
> Because people had problems with that in the past and asked on the
> list for the reason and the answer (by Monty, I think) was that the
> join order optimization takes the additional time (in comparison to a
> STRAIGHT_JOIN). The delay was in the order of seconds, IIRC.

Ah. Thanks.

> You'd have to try out. Since the time is doubling with each table,
> let's say 31 tables would need 100 secs, with 20 tables you would
> hardly notice it, as it would be only 1/2048 of that, i.e. less than
> 0.05 secs.

Yes, I see. (31-20=11, 2048 = 11 bits)

> You see why allowing 64 tables futile?

Well... apparently it _is_ allowed on 64 bit platforms... :)

But I understand why implementing it for 32 bit platform is not a
prioritized task for the developers.

> Presuming processing one order
> would be as simply as one plain instruction, the join order optimizer
> would need on a Pentium III 500 (225.4 MIPS):
>
> #tables
>  10      0.0000045 secs
>  20      0.0047 secs
>  30      4.76 secs
>  40      1.35 hours
>  50     57.81 days
>  60    162.20 years
>
> Of course, real scaling will differ as pure MIPS don't show real
> speed. But you get the idea.

Yupp. Exponential growth.

> For your purpose, you have to try out, if speed decrease with 30
> tables matters to you. Please post any result you find.

Yes, I will do some tests during the next couple of weeks. I am currently
working on the import routines, I should be able to test some queries pretty
soon.

Thanks a lot! :)

--
Roger

Thread
Maximum JOINED tablesArthur Fuller5 Jun
  • Re: Maximum JOINED tablesRoger Baklund5 Jun
RE: Maximum JOINED tablesWalt Weaver5 Jun
  • RE: Maximum JOINED tablesAndrew Hazen5 Jun
RE: Maximum JOINED tablesWalt Weaver5 Jun
RE: Maximum JOINED tablesWalt Weaver5 Jun
  • Re: Maximum JOINED tablesSabine Richter5 Jun
    • Re: Maximum JOINED tablesDan Nelson5 Jun
  • Re: Maximum JOINED tablesRoger Baklund5 Jun
    • Re: Maximum JOINED tablesHarald Fuchs6 Jun
      • RE: Maximum JOINED tablesRoger Baklund6 Jun
        • Re: RE: Maximum JOINED tablesVictoria Reznichenko6 Jun
          • Re: Maximum JOINED tablesRoger Baklund6 Jun
            • Re: Maximum JOINED tablesBenjamin Pflugmann7 Jun
              • Re: Maximum JOINED tablesRoger Baklund7 Jun
                • Re: Re: Maximum JOINED tablesVictoria Reznichenko7 Jun
                  • Re: Maximum JOINED tablesRoger Baklund7 Jun
                • Re: Maximum JOINED tablesBenjamin Pflugmann7 Jun
                  • Re: Maximum JOINED tablesRoger Baklund8 Jun
                    • Not using indexes???Jon Frisby8 Jun
                      • Re: Not using indexes???Erv Young8 Jun
                      • Re: Not using indexes???bvyas38 Jun
                        • RE: Not using indexes???Jon Frisby8 Jun
                          • Re: Not using indexes???Benjamin Pflugmann8 Jun
                            • Re: Not using indexes???Erv Young10 Jun
                            • RE: Not using indexes???Jon Frisby10 Jun
                              • php+mysql+multiple lines qrycristian ditoiu10 Jun
                                • Re: php+mysql+multiple lines qryBrent Baisley10 Jun
                                  • Re: php+mysql+multiple lines qrycristian ditoiu10 Jun