List:General Discussion« Previous MessageNext Message »
From:Kevin Cowley Date:March 7 2005 6:31pm
Subject:RE: Join Limits
View as plain text  
Unfortunately both limits are getting in our way.=20

We have approximately 32,000 variables scattered across a number of
tables that we need to convert to bitmaps. The problem is that about
1500 of these variables need to go in a single bitmap hence the problems
with the 1024/64 column/table limit.

If you're correct and its using a bitmap in the optimizer, then it means
its possible to extend the value by replacing the bitmap with a
structure and a clever set of functions. I've used this technique in
another application to overcome a 32 bit limit - its probably going to
be a bastard of a job to rewrite the relevant parts of MySql though.

Kevin Cowley
Product Development
Alchemetrics Ltd
SMARTER DATA , FASTER
Tel: 0118 902 9000 (swithcboard)
Tel: 0118 902 9099 (direct)
Web: www.alchemetrics.co.uk
Email: kevin.cowley@stripped

> -----Original Message-----
> From: Eric Bergen [mailto:eric.bergen@stripped]
> Sent: 07 March 2005 18:16
> To: Kevin Cowley
> Cc: mysql@stripped
> Subject: Re: Join Limits
>=20
> The join table limit in MySQL is dictated by the arch that's running
> on. 32 tables for 32bit and 64 tables for 64bit (Somebody correct me
> if I'm wrong). I believe this is due to using a bitmap inside the join
> optimizer to keep track of tables. 64 tables is a very hefty query it
> makes me think that maybe you are doing something wrong when designing
> your application to need a join that big. When the optimizer optimizes
> a query it checks every possible execution path through every table so
> the more joins you have the more paths the opimizer has to check to
> find the optimal one (This isn't entirely true but close enough for my
> argument here).
>=20
> Needing more than 1024 columns in a table also seems like bad design.
> Maybe you can detail more of what you are doing and why you need so
> many columns
>=20
> -Eric
>=20
>=20
> On Mon, 7 Mar 2005 12:21:52 -0000, Kevin Cowley
> <kevin.cowley@stripped> wrote:
> > Does anyone know if there is a method of circumventing or changing
the
> > default join limits of 64 tables or 1024 columns? We're running
Mysql
> > 4.1.4 using MyISAM tables
> >
> > Kevin Cowley
> > Product Development
> > Alchemetrics Ltd
> > SMARTER DATA , FASTER
> > Tel: 0118 902 9000 (swithcboard)
> > Tel: 0118 902 9099 (direct)
> > Web: www.alchemetrics.co.uk <http://www.alchemetrics.co.uk>
> > Email: kevin.cowley@stripped
> >
> >
>
************************************************************************
**
> ************
> > ALCHEMETRICS LIMITED (ALCHEMETRICS)
> > Mulberry Park, Fishponds Road, Wokingham, Berkshire, RG41 2GX
> > Tel:  +44 (0) 118 902 9000    Fax:  +44 (0) 118 902 9001
> > This e-mail is confidential and is intended for the use of the
addressee
> only.
> > If you are not the intended recipient, you are hereby notified that
you
> must
> > not use, copy, disclose, otherwise disseminate or take any action
based
> on this e-mail or any information herein.
> > If you receive this transmission in error, please notify the sender
> > immediately by reply e-mail or by using the contact details above
and
> then
> > delete this e-mail.
> > Please note that e-mail may be susceptible to data corruption,
> interception and unauthorised amendment.  Alchemetrics does not accept
any
> liability for
> > any such corruption, interception, amendment or the consequences
> thereof.
> >
>
************************************************************************
**
> ************
> >
> >
>=20
>=20
> --
> Eric Bergen
> eric.bergen@stripped
> http://www.ebergen.net


***************************************************************************=
***********
ALCHEMETRICS LIMITED (ALCHEMETRICS)
Mulberry Park, Fishponds Road, Wokingham, Berkshire, RG41 2GX
Tel:  +44 (0) 118 902 9000    Fax:  +44 (0) 118 902 9001
This e-mail is confidential and is intended for the use of the addressee on=
ly.
If you are not the intended recipient, you are hereby notified that you mus=
t=20
not use, copy, disclose, otherwise disseminate or take any action based on=
 this e-mail or any information herein.
If you receive this transmission in error, please notify the sender
immediately by reply e-mail or by using the contact details above and then
delete this e-mail.
Please note that e-mail may be susceptible to data corruption, interception=
 and unauthorised amendment.  Alchemetrics does not accept any liability fo=
r=20
any such corruption, interception, amendment or the consequences thereof.
***************************************************************************=
***********

Thread
Join LimitsKevin Cowley7 Mar
Re: Join LimitsEric Bergen7 Mar
RE: Join LimitsKevin Cowley7 Mar
Re: Join LimitsGreg Fortune7 Mar
  • Re: Join LimitsSGreen7 Mar