Hi Alfredo, What an amazing piece of code! The join statement is a computation on both sides, so therefore no index is of material use. Consequently a table scan would be required.
Have you tried EXPLAIN? Regards, =dn > -----BEGIN PGP SIGNED MESSAGE----- > Hash: SHA1 > > Hi: > > Using MySQL version 3.23.39-Max, SuSE 7.2 > > I have two tables, cbtran with 46564 rows and cbma with 4184 rows. > Both tables are indexed. > > The following query: > > 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.cuent a) > 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). > > what this query is supposed to do is select the rows from table > cbtran that belong to company (empresa) 1, and that have a month > value of 1 and a year value of 2002, and then select from table cbma > the row that matches the account number represented by tr.cuenta. > > the same query but without involving cbma at all takes only 3 seconds > and returns 12,766 rows also. > > What am i doing wrong? > > Thank you. > - -- > Alfredo J. Cole > http://www.acyc.com (Accounting Systems) > http://www.clshonduras.com (Linux Hardware) > PGP Key available from certserver.pgp.com > -----BEGIN PGP SIGNATURE----- > Version: GnuPG v1.0.5 (GNU/Linux) > Comment: For info see http://www.gnupg.org > > iD8DBQE8fPM1u5DxuPWE298RApA5AJ9rpiPiHWzgG6f/ueFDKF9uCvSLsgCeLs4b > 90kfKS03SGoTngOM6wsUYyM= > =jcfG > -----END PGP SIGNATURE----- > > --------------------------------------------------------------------- > 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 > > --------------------------------------------------------------------- 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
