Slow MYSQL performance due to sub queries Here is my query:

SELECT BAL,NAME,
   (SELECT SUM(DR) FROM transaction WHERE NAME=TNAME AND DATE &lt; '2000-05-01') AS DR ,
   (SELECT SUM(CR) FROM transaction WHERE NAME=TNAME AND DATE &lt; '2000-05-01') AS CR
FROM main
GROUP BY MARK,NAME WITH ROLLUP


This query takes at least 20 seconds and sometimes even up to 45 seconds when  ‘DATE’ increases(example:2012-05-01).

There are about 500,000 records in the transaction table. After hours of testing I think the problem might be with the 2 sub queries:

(SELECT SUM(DR) FROM transaction WHERE NAME=TNAME AND DATE &lt; '2000-05-01') AS DR ,
(SELECT SUM(CR) FROM transaction WHERE NAME=TNAME AND DATE &lt; '2000-05-01') AS CR


I tried adding an index to DATE and TNAME, it speeded things up a little bit, but still not enough.Would be great if I could atleast shave off atleast 10 more seconds
Is there anything I could do to make it faster?

Table details:

 CREATE TABLE ` transaction` (
  `DATE` DATE NULL DEFAULT NULL,
  `DOCNO` VARCHAR(10) NULL DEFAULT NULL,
  `TCODE` VARCHAR(6) NULL DEFAULT NULL,
  `TNAME` VARCHAR(50) NULL DEFAULT NULL,
  `CR` DECIMAL(12,2) NULL DEFAULT NULL,
  `DR` DECIMAL(12,2) NULL DEFAULT NULL,
   INDEX `TNAME` (`TNAME`),
   INDEX `DATE` (`DATE`),
   INDEX `CR` (`CR`),
   INDEX `DR` (`DR`)
  )
  COLLATE='latin1_swedish_ci'
  ENGINE=InnoDB;


  CREATE TABLE `main` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `CODE` VARCHAR(50) NOT NULL,
    `NAME` VARCHAR(70) NULL DEFAULT NULL,
    `BAL` DECIMAL(12,2) NULL DEFAULT '0.00',
    `MARK` VARCHAR(35) NULL DEFAULT NULL,
    PRIMARY KEY (`CODE`),
    UNIQUE INDEX `NAME` (`NAME`),
    PRIMARY KEY (`id`),
    INDEX `MARK` (`MARK`)
   )
   COLLATE='utf8_general_ci'
   ENGINE=InnoDB
   ROW_FORMAT=COMPACT
   AUTO_INCREMENT=545451;
Of course it is the subqueries' fault. For each row of table main, this query goes through the whole transactions table twice (once for each subquery). That's a lot of load.

How about a simple join? This should have the same results and take much less than 50% of your processing time even with no indexes.

SELECT main.BAL,main.NAME,SUM(transaction.DR),SUM(transaction.CR)
FROM main
LEFT JOIN transaction on transaction.TNAME = main.NAME and transaction.DATE &lt; '2000-05-01' 
GROUP BY main.MARK,main.NAME WITH ROLLUP