Uptimize: join table on if()

[Mogens Melander]

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.