From: Peter Brawley
Date: December 8 2008 3:46pm
Subject: Re: Uptimize: join table on if()
List-Archive: http://lists.mysql.com/mysql/215532
Message-Id: <493D4155.7050500@earthlink.net>
MIME-Version: 1.0
Content-Type: multipart/alternative;
 boundary="------------010309090204090302070504"

--------------010309090204090302070504
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit

 >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
>
>   

--------------010309090204090302070504--