List:General Discussion« Previous MessageNext Message »
From:Brent Baisley Date:December 8 2008 9:48pm
Subject:Re: Uptimize: join table on if()
View as plain text  
On Mon, Dec 8, 2008 at 7:14 AM, Mogens Melander <mogens@stripped> 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
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