>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
