Thanks for the suggestions.
My original query returned 5784 rows in 184.89 sec. Brent Baisley's variant
returned 5784 rows in 1.34 sec. and Peter Brawley's solution resulted in
5780 rows in 1.46 sec. I'm officially impressed :)
I can't figure out why the "UNION" solution is missing 4 rows. I'll include
the 3 complete statements, so maybe someone smarter than me can figure out
why there's a difference in the result.
===============[ Brent Baisley ]============================================
select m.code, m.parent,
if( m.parent > 0, t.line1, t1.line1 ) AS line1,
if( m.parent > 0, t.line2, t1.line2 ) AS line2,
if( m.parent > 0, t.line3, t1.line3 ) AS line3,
if( m.parent > 0, t.line4, t1.line4 ) AS line4,
if( m.parent > 0, t.line5, t1.line5 ) AS line5,
if( m.parent > 0, t.line6, t1.line6 ) AS line6,
if( m.parent > 0, t.line7, t1.line7 ) AS line7,
if( m.parent > 0, t.line8, t1.line8 ) AS line8,
if( m.parent > 0, t.line9, t1.line9 ) AS line9,
if( m.parent > 0, t.line10, t1.line10 ) AS line10,
u.unit, u.pack
from main m
left join olomulti t
on t.code = m.parent and t.country='dk'
left join olomulti t1
on t1.code=m.code and t1.country='dk'
left join unit u
on u.code=m.code and u.country='dk'
where m.activedk = 'on' and m.olomultidk = 'on'
order by m.code;
===============[ Peter Brawley ]============================================
select m.code, m.parent, t.line1, t.line2, t.line3, t.line4,
t.line5, t.line6, t.line7, t.line8, t.line9, t.line10, u.unit, u.pack
from main m
left join olomulti t
on t.code = m.parent and t.country='dk'
left join unit u
on u.code=m.code and u.country='dk'
where m.parent>0
and m.activedk = 'on'
and m.olomultidk = 'on'
and t.line1 is not null
union
select m.code, m.parent, t.line1, t.line2, t.line3, t.line4,
t.line5, t.line6, t.line7, t.line8, t.line9, t.line10, u.unit, u.pack
from main m
left join olomulti t
on t.code = m.code and t.country='dk'
left join unit u
on u.code=m.code and u.country='dk'
where m.parent=0
and m.activedk = 'on'
and m.olomultidk = 'on'
and t.line1 is not null
order by code;
===============[ My Self ]============================================
select m.code, m.parent, concat('\'',lpad(m.code,18,'0'),'\'') lcode, t.line1,
t.line2,
t.line3, t.line4, t.line5, t.line6, t.line7, t.line8, t.line9, t.line10,
u.unit, u.pack
from main m
left join olomulti t
on ( if( m.parent > 0, t.code = m.parent, t.code = m.code ) and t.country='dk'
)
left join unit u
on u.code=m.code and u.country='dk'
where m.activedk = 'on' and m.olomultidk = 'on'
order by m.code;
======================================================================
On Mon, December 8, 2008 22:48, Brent Baisley wrote:
> 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
>
--
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]
