>The idea is that data in tdata "might" be shared between 2 or more
records in main.
Is this any faster?
select m.code, m.parent, t.data
from main m
left join tdata t
on t.code = m.parent and t.country='dk'
where m.parent=0 and m.active = 'on' and m.tdataon = 'on'
union
select m.code, m.parent, t.data
from main m
left join tdata t
on t.code = m.code and t.country='dk'
where m.parent=0 and m.active = 'on' and m.tdataon = 'on'
order by code;
Or this?
select m.code, m.parent, t.data
from main m
left join tdata t
on t.code = IF(m.parent>0,m.parent,m.code) and t.country='dk'
where m.active = 'on' and m.tdataon = 'on'
order by m.code;
PB
-----
Mogens Melander 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;
>
>
> ------------------------------------------------------------------------
>
>
> Internal Virus Database is out of date.
> Checked by AVG - http://www.avg.com
> Version: 8.0.176 / Virus Database: 270.9.11/1820 - Release Date: 11/29/2008 6:52 PM
>
>
