List:General Discussion« Previous MessageNext Message »
From:Roger Baklund Date:June 7 2002 9:43am
Subject:Re: Maximum JOINED tables
View as plain text  
* Benjamin Pflugmann
> On Thu 2002-06-06 at 19:17:10 +0200, roger.baklund@stripped wrote:
> [...]
> > But... is this the only feature of mysql which is dependant on the
> > processor architecture?
> See it the other way: You are not garantueed more than 31 tables in a
> join. If you happen to have a 64Bit architecture, you can use more.

Yes... a positive attitude makes life easier. :)

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:

mysql is considered to be platform independant, and I suppose MySQL AB is
trying to keep it that way...?

What (other) features of mysql are dependant on the processor architecture?

Will the max number of joined tables (on 32-bit platform) stay at 31 in
version 4.x, or is it planned to increase this limit?

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

> > I'm kind of nervous about this, because I'm currently working on a 'very
> > normalized' database schema, and it is possible I will need more than 31
> > tables in a single select... including multiple joins to the
> > same tables, of
> > course. It would be very bad if my design turned out to be 64-bit
> > dependant...
> AFAIK, if you need so many tables, you get a major problem: MySQL
> figures out the best join order by trying all combinations. This
> becomes increasingly slow as you are going to join more tables. In
> other words: Trying to use 30 tables in one select probably will be
> too slow as that it would matter whether so many tables are allowed in
> a join or not.


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?

How slow are we talking here...? Normally the join order is decided within
0.001 sec... (I'm only guessing) When you say slow, do you mean 0.01, or 0.1
or 1.0 sec or what?

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...?

> Of course, you could work around it by using STRAIGHT_JOIN, but I
> assume it is to cumbersome to figure out the optimal join order for
> all SELECTs.

I was kind of hoping mysql would do that for me... but I guess I could use a
combination, using STRAIGHT_JOIN for some of the joins.

I can split my query in two (or more), but I hope I don't have to, because I
suspect this will slow down the performance, and I need to "always" have a
result within 0.5 sec...


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