Re: Uptimize: join table on if()
Hmmm, no. That's not it. The 2 queries using if() return the right number of rows. The union return too few. Anyway. I ended up with a query that runs in about 1 sec. compared to the original that ran about 3 min. I'm happy :) Thanks a bunch. On Tue, December 9, 2008 14:53, Thomas Pundt wrote: > On Dienstag, 9. Dezember 2008, Mogens Melander wrote: > | 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. > > Without having "studied" your query, my guess would be: double rows. UNION > eliminates those; if you need them, use "UNION ALL". Just a guess though... > > Ciao, > Thomas > > -- > Thomas Pundt <[EMAIL PROTECTED]> http://rp-online.de/ -- 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]
Re: Uptimize: join table on if()
On Dienstag, 9. Dezember 2008, Mogens Melander wrote: | 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. Without having "studied" your query, my guess would be: double rows. UNION eliminates those; if you need them, use "UNION ALL". Just a guess though... Ciao, Thomas -- Thomas Pundt <[EMAIL PROTECTED]> http://rp-online.de/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Uptimize: join table on if()
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
Re: Uptimize: join table on if()
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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Uptimize: join table on if()
>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
Uptimize: join table on if()
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; -- 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]
