List:General Discussion« Previous MessageNext Message »
From:Greg Fortune Date:March 7 2005 6:46pm
Subject:Re: Join Limits
In-reply-to:
<ED4FAA3F200DB3479E432945C24704CD026BDB@alchexch01.alchemetrics.local>
View as plain text  
Any chance of condensing some of the flag fields into bit fields?  
Alternatively, can you represent the variables as (rule id, variable name, 
variable value) in a single table rather than using lots of columns/tables?

Greg Fortune

On Monday 07 March 2005 10:31 am, Kevin Cowley wrote:
> Unfortunately both limits are getting in our way.
>
> 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
> >
> > 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).
> >
> > 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
> >
> > -Eric
> >
> >
> > 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.
>
> ************************************************************************
> **
>
> > ************
> >
> >
> >
> >
> > --
> > 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
> 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.
> ***************************************************************************
>***********
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    http://lists.mysql.com/mysql?unsub=1
Thread
Join LimitsKevin Cowley7 Mar
Re: Join LimitsEric Bergen7 Mar
RE: Join LimitsKevin Cowley7 Mar
Re: Join LimitsGreg Fortune7 Mar
  • Re: Join LimitsSGreen7 Mar