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]
