List:General Discussion« Previous MessageNext Message »
From:Peter Brawley Date:December 8 2008 3:46pm
Subject:Re: Uptimize: join table on if()
View as plain text  
 >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
>
>   

Thread
Uptimize: join table on if()Mogens Melander8 Dec
  • Re: Uptimize: join table on if()Peter Brawley8 Dec
  • Re: Uptimize: join table on if()Brent Baisley8 Dec
    • Re: Uptimize: join table on if()Mogens Melander9 Dec
      • Re: Uptimize: join table on if()Thomas Pundt9 Dec
        • Re: Uptimize: join table on if()Mogens Melander9 Dec