Hi list I have this statement that really need optimizing. The result is about 5500 rows, and it runs for about 3-5 minutes. If i do the same in a PHP script (outer loop, inner loop) it run in 20 sec.
The idea is that data in tdata "might" be shared between 2 or more records in main. The main.parent field is a pointer to main.code, so if main.parent is positive, i need to retrieve data linked to parent. Did i miss something? select m.code, m.parent, t.data from main m left join tdata t on ( if( m.parent > 0, t.code = m.parent, t.code = m.code ) and t.country='dk' ) where m.active = 'on' and m.tdataon = 'on' order by m.code; CREATE TABLE `main` ( `code` int(10) unsigned NOT NULL default '0', `parent` int(10) unsigned NOT NULL default '0', `active` varchar(2) NOT NULL, `tdataon` varchar(2) NOT NULL default '', PRIMARY KEY (`code`), KEY `parent` (`parent`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; CREATE TABLE `data` ( `code` int(10) unsigned NOT NULL default '0', `country` varchar(2) NOT NULL default '', `data` varchar(130) NOT NULL default '', PRIMARY KEY (`code`,`country`), KEY `code` (`code`), KEY `country` (`country`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; -- Later Mogens Melander -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
