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 <[EMAIL PROTECTED]> 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: [EMAIL PROTECTED] > > > > > -----Original Message----- > > > From: Eric Bergen [mailto:[EMAIL PROTECTED] > > > Sent: 07 March 2005 18:16 > > > To: Kevin Cowley > > > Cc: mysql@lists.mysql.com > > > 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 > > > > > > <[EMAIL PROTECTED]> 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: [EMAIL PROTECTED] > > > > ************************************************************************ > > ** > > > > > ************ > > > > > > > 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 > > > [EMAIL PROTECTED] > > > 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/[EMAIL PROTECTED] > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] >
