[allang]

need help.. urgent!

I need to write a select statement to get the following results.
I tried using joins, but it just takes all my cpu time, and takes forever to process.



TABLES:
-----------------
(about 500 records)
CREATE TABLE `A` (
`ACCNT_CODE` varchar(50) default NULL,
`TRANS_DATE` datetime default NULL,
`AMOUNT` double default NULL,
`TREFERENCE` varchar(50) default NULL,
`OTHER_AMT` double default NULL,
`TRANS_ID` int(11) NOT NULL auto_increment,
KEY `TRANS_ID` (`TRANS_ID`)
) TYPE=MyISAM AUTO_INCREMENT=1 ;

(about 50000 records)
CREATE TABLE `B` (
`ACCNT_CODE` varchar(255) default NULL,
`ACCNT_NAME` varchar(255) default NULL,
`AN_A2` varchar(255) default NULL,
`BADFLAG` varchar(50) default 'N',
`DATE_ACTION` datetime default '2001-01-19 00:00:00',
) TYPE=MyISAM;

(about 5000 records)
CREATE TABLE `C` (
`CATEGORY` varchar(255) default NULL,
`CODE` varchar(255) default NULL,
`NAME` varchar(255) default NULL
) TYPE=MyISAM;
------------------------




RESULT NEEDED:
---------------------
ACCNT_CODE,

ACCNT_NAME,

TOTAL_AMOUNT,
SUM(A.AMOUNT) GROUP BY ACCNT_CODE,

OVERDUE_AMNT,
SUM(A.AMOUNT) WHERE TRANS_DATE > 'x' GROUP BY ACCNT_CODE

DATE_ACTION,

NAME,
C.CODE=B.AN_2 AND C.CATEGORY='x'
----------------------------------------------


WHERE A.ACCNT_CODE=B.ACCNT_CODE
GROUP BY ACCNT_CODE


Anyone has a solution?

Thanks

[vanekl]

Originally posted by allang
need help.. urgent!

I need to write a select statement to get the following results.
I tried using joins, but it just takes all my cpu time, and takes forever to process.



TABLES:
-----------------
(about 500 records)
CREATE TABLE `A` (
`ACCNT_CODE` varchar(50) default NULL,
`TRANS_DATE` datetime default NULL,
`AMOUNT` double default NULL,
`TREFERENCE` varchar(50) default NULL,
`OTHER_AMT` double default NULL,
`TRANS_ID` int(11) NOT NULL auto_increment,
KEY `TRANS_ID` (`TRANS_ID`)
) TYPE=MyISAM AUTO_INCREMENT=1 ;

(about 50000 records)
CREATE TABLE `B` (
`ACCNT_CODE` varchar(255) default NULL,
`ACCNT_NAME` varchar(255) default NULL,
`AN_A2` varchar(255) default NULL,
`BADFLAG` varchar(50) default 'N',
`DATE_ACTION` datetime default '2001-01-19 00:00:00',
) TYPE=MyISAM;

(about 5000 records)
CREATE TABLE `C` (
`CATEGORY` varchar(255) default NULL,
`CODE` varchar(255) default NULL,
`NAME` varchar(255) default NULL
) TYPE=MyISAM;
------------------------




RESULT NEEDED:
---------------------
ACCNT_CODE,

ACCNT_NAME,

TOTAL_AMOUNT,
SUM(A.AMOUNT) GROUP BY ACCNT_CODE,

OVERDUE_AMNT,
SUM(A.AMOUNT) WHERE TRANS_DATE > 'x' GROUP BY ACCNT_CODE

DATE_ACTION,

NAME,
C.CODE=B.AN_2 AND C.CATEGORY='x'
----------------------------------------------


WHERE A.ACCNT_CODE=B.ACCNT_CODE
GROUP BY ACCNT_CODE


Anyone has a solution?

Thanks

The reason why it takes a long time is because:

1. you left out primary keys on all your tables
2. you should be joining on fields that have indexes
associated with them, ideally the primary key field
3. you're joining on fields that are varchars, when
joins go much faster if you join on fields that are integers.
You should consider adding an aritificial key field to
tables B and C (id INT(4) NOT NULL AUTO_INCREMENT),
and join on this field instead.

Go back and redefine your tables so that they fix
the above problems and your joins will go quickly.