Alfredo Cole <[EMAIL PROTECTED]> wrote: > select tr.*,ma.nombre from cbtran as tr, cbma as ma where > tr.empresa='1' and tr.mes='1' and tr.anio='2002' and > >concat(ma.empresa,ma.clase,ma.tipo,ma.mayor,ma.grupo,ma.costo,ma.cuenta,ma.subcuenta)=concat(tr.empresa,tr.cuenta) > > order by tr.anio, tr.mes, tr.dia, tr.partida > > takes over 19 minutes to complete on my laptop (Celeron 550 Mhz, 160 > MB RAM, 20 GB HD).
The problem is that the join occurs through those concatenations, which have to be calculated for each pair of rows. That means the indexes can't be used. It would be a lot better if there were columns corresponding between the two tables -- for example, if tr.empresa was the same as ma.empresa and tr.cuenta was the same as ma.cuenta. Is there any way that you can include columns in cbma where the concatenation has already been done -- that is, one for empresa+clase+tipo+grupo+costo (which could be paired with tr.empresa) and one for cuenta+subcuenta (which could be paired with tr.cuenta)? Failing that, perhaps you could split up the columns for empresa and cuenta in cbtran so that they matched the columns in cbma. That would not only speed up the query greatly (assuming appropriate indexes), but also eliminate the confusion of having identically named columns in two tables that don't mean the same thing. -- Keith C. Ivey <[EMAIL PROTECTED]> Washington, DC --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
