List:General Discussion« Previous MessageNext Message »
From:SGreen Date:March 7 2005 7:11pm
Subject:Re: Join Limits
View as plain text  
I agree with Greg, your data structure may be getting in your way. It's 
more "normalized" to make a list of values into rows of values, not into 
lots of columns. 

I may have had a similar design problem as you are facing. I have to deal 
with LOADS of laboratory analysis data (lots of data points for each 
sample) and rather than build a table "wide" enough to hold every possible 
analysis result (one row per sample), I normalized the data into two 
tables a "sample" table and a "results" table. I have been having 
excellent results with maintaining the data, with data retrieval times, 
and maintaining the user interface code.  Everything is so much more 
simple than if I had used a single row per result.

If you can, will you please post an example of your data structure? You 
can change all of the names you like so long as we get an idea of what you 
are trying to deal with. How do you need to use this data? I ask that 
because you can usually optimize query speed with good database design. 
What is an example of the query you are trying to write? Is this a common 
query or a special case?




Greg Fortune <gfortune@stripped> wrote on 03/07/2005 01:46:18 PM:

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