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
But then people would start to ask, why this limit is artificially low
on 64 bit platforms.
> What (other) features of mysql are dependant on the processor
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.
> 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 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).
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.
> 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.
The client will have to wait too long, no matter where the time is spent.
> 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.
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
You see why allowing 64 tables futile? 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):
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.
For your purpose, you have to try out, if speed decrease with 30
tables matters to you. Please post any result you find.