On Mon, Dec 8, 2008 at 7:14 AM, Mogens Melander <[EMAIL PROTECTED]> wrote: > 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; > > --
What you are doing wrong is putting a condition on the JOIN. This prevent MySQL from optimizing the query because it has to check every record to determine the join. Put the condition in the field list you pull. Alias the table you are joining on so you can join it twice, one for each condition. select m.code, m.parent, if( m.parent > 0, t.data, t1.data ) AS data from main m left join tdata t on t.code = m.parent and t.country='dk' left join tdata t1 on t1.code=m.code and t1.country='dk' where m.active = 'on' and m.tdataon = 'on' order by m.code; That may not be completely correct. What you are doing is getting 2 copies of the data field and conditional adding the one you need to the retrieved record. Brent Baisley -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
