Union Statement Poor Performance

"How-To-Repeat:"

hi,

We have a rather large union statement that takes nearly 7 *minutes* to
return on MySql, 5 seconds on MSQL and 5 seconds on Postgresql.

The tables have around 5-10 thousand rows. To be fair the union
statement had to be changed from using the IN() statement to basic sql
syntax which kills performance on MSQL and Postgresql as well but only
by 3-5 seconds whereas MySql takes over 5 *minutes*.

Here is the Union stetment, I can provide you with the data files so
you guys can run some test. I should mention that our product is in use
by 10,000+ Including Lexis-Nexis, Toshiba and the DOD. The product
"World Server" currently runs on everything from a mac, toshiba sg20
appliance to blad servers using MSQL/Postgres on Linux/MS//Mac. We
would like to include MySql as an option for our clients because the
licensing is less expensive to the alternative Microsoft MSQL. Any
feedback would be great.

MySql 4.0.x gamma
Mac OS X 1ghz 1gb of ram

Frank A. Rivera -LOGICBit Corporation
DATA.TXT Corporation www.timematters.com

----------------- How-To-Repeat:

SELECT 0 AS a,0 AS b,'' AS c,`matter`.`staff` AS d,`matter`.`ccode` AS
e,`matter`.`client` AS f,`matter`.`mat_ref` AS g,`matter`.`mat_no` AS
h,'' AS i,4 AS j,1 AS k,0 AS l,`matter`.`sysid` AS m,`matter`.`archive`
AS n,`matter`.`status` AS o,'' AS
`rcode`,`matter`.`con_id`,`matter`.`sysid` AS
`mat_id`,`matter`.`private` FROM `matter`,`contact` WHERE
lower(`contact`.`sysid`)=lower('EC4738F45B190490') AND
lower(`matter`.`sysid`)=lower(`contact`.`mat_id`)

UNION SELECT 0 AS a,0 AS b,`contact`.`last_name` AS c,`contact`.`staff`
AS d,`contact`.`ccode` AS e,`contact`.`first_name` AS
f,`contact`.`mat_ref` AS g,`contact`.`mat_no` AS h,'' AS i,3 AS j,1 AS
k,0 AS l,`contact`.`sysid` AS m,`contact`.`archive` AS
n,`contact`.`status` AS o,'' AS `rcode`,`contact`.`sysid` AS
`con_id`,`contact`.`mat_id`,`contact`.`private` FROM `contact`,`matter`
WHERE lower(`matter`.`sysid`)=lower('EC4738F45B190490') AND
lower(`contact`.`sysid`)=lower(`matter`.`con_id`)

UNION SELECT `event`.`date` AS a,`event`.`time` AS b,`event`.`desc` AS
c,`event`.`staff` AS d,`event`.`ccode` AS e,`event`.`client` AS
f,`event`.`mat_ref` AS g,`event`.`mat_no` AS h,'' AS i,1 AS j,1 AS k,0
AS l,`event`.`sysid` AS m,`event`.`archive` AS n,`event`.`status` AS
o,'' AS `rcode`,`event`.`con_id`,`event`.`mat_id`,`event`.`private`
FROM `event`,`relatea` WHERE
lower(`event`.`sysid`)=lower(`relatea`.`mid`) AND
lower(`relatea`.`sid`)=lower('C365834955124653') AND
lower(`relatea`.`mid`)<>lower('EC4738F45B190490') OR
lower(`relatea`.`sid`)=lower('A96FD8F4519C2915') AND
lower(`relatea`.`mid`)<>lower('EC4738F45B190490') AND
`event`.`archive`=0

UNION SELECT `todo`.`date` AS a,0 AS b,`todo`.`desc` AS
c,`todo`.`staff` AS d,`todo`.`ccode` AS e,`todo`.`client` AS
f,`todo`.`mat_ref` AS g,`todo`.`mat_no` AS h,`todo`.`priority` AS i,2
AS j,1 AS k,0 AS l,`todo`.`sysid` AS m,`todo`.`archive` AS
n,`todo`.`status` AS o,'' AS
`rcode`,`todo`.`con_id`,`todo`.`mat_id`,`todo`.`private` FROM
`todo`,`relatea` WHERE lower(`todo`.`sysid`)=lower(`relatea`.`mid`)
AND lower(`relatea`.`sid`)=lower('C365834955124653') AND
lower(`relatea`.`mid`)<>lower('EC4738F45B190490') OR
lower(`relatea`.`sid`)=lower('A96FD8F4519C2915') AND
lower(`relatea`.`mid`)<>lower('EC4738F45B190490') AND
`todo`.`archive`=0

UNION SELECT 0 AS a,0 AS b,`contact`.`last_name` AS c,`contact`.`staff`
AS d,`contact`.`ccode` AS e,`contact`.`first_name` AS
f,`contact`.`mat_ref` AS g,`contact`.`mat_no` AS h,'' AS i,3 AS j,1 AS
k,0 AS l,`contact`.`sysid` AS m,`contact`.`archive` AS
n,`contact`.`status` AS o,'' AS `rcode`,`contact`.`sysid` AS
`con_id`,`contact`.`mat_id`,`contact`.`private` FROM
`contact`,`relatea` WHERE
lower(`contact`.`sysid`)=lower(`relatea`.`mid`) AND
lower(`relatea`.`sid`)=lower('C365834955124653') AND
lower(`relatea`.`mid`)<>lower('EC4738F45B190490') OR
lower(`relatea`.`sid`)=lower('A96FD8F4519C2915') AND
lower(`relatea`.`mid`)<>lower('EC4738F45B190490') AND
`contact`.`archive`=0

UNION SELECT 0 AS a,0 AS b,'' AS c,`matter`.`staff` AS
d,`matter`.`ccode` AS e,`matter`.`client` AS f,`matter`.`mat_ref` AS
g,`matter`.`mat_no` AS h,'' AS i,4 AS j,1 AS k,0 AS l,`matter`.`sysid`
AS m,`matter`.`archive` AS n,`matter`.`status` AS o,'' AS
`rcode`,`matter`.`con_id`,`matter`.`sysid` AS
`mat_id`,`matter`.`private` FROM `matter`,`relatea` WHERE
lower(`matter`.`sysid`)=lower(`relatea`.`mid`) AND
lower(`relatea`.`sid`)=lower('C365834955124653') AND
lower(`relatea`.`mid`)<>lower('EC4738F45B190490') OR
lower(`relatea`.`sid`)=lower('A96FD8F4519C2915') AND
lower(`relatea`.`mid`)<>lower('EC4738F45B190490') AND
`matter`.`archive`=0

UNION SELECT `event`.`date` AS a,`event`.`time` AS b,`event`.`desc` AS
c,`event`.`staff` AS d,`event`.`ccode` AS e,`event`.`client` AS
f,`event`.`mat_ref` AS g,`event`.`mat_no` AS h,'' AS i,1 AS j,2 AS k,0
AS l,`event`.`sysid` AS m,`event`.`archive` AS n,`event`.`status` AS
o,'' AS `rcode`,`event`.`con_id`,`event`.`mat_id`,`event`.`private`
FROM `event`,`relates` WHERE
lower(`event`.`sysid`)=lower(`relates`.`sid`) AND
lower(`relates`.`mid`)=lower('EC4738F45B190490') AND
`relates`.`fieldno`=0 AND `event`.`archive`=0

UNION SELECT `todo`.`date` AS a,0 AS b,`todo`.`desc` AS
c,`todo`.`staff` AS d,`todo`.`ccode` AS e,`todo`.`client` AS
f,`todo`.`mat_ref` AS g,`todo`.`mat_no` AS h,`todo`.`priority` AS i,2
AS j,2 AS k,0 AS l,`todo`.`sysid` AS m,`todo`.`archive` AS
n,`todo`.`status` AS o,'' AS
`rcode`,`todo`.`con_id`,`todo`.`mat_id`,`todo`.`private` FROM
`todo`,`relates` WHERE lower(`todo`.`sysid`)=lower(`relates`.`sid`)
AND lower(`relates`.`mid`)=lower('EC4738F45B190490') AND
`relates`.`fieldno`=0 AND `todo`.`archive`=0

UNION SELECT 0 AS a,0 AS b,`contact`.`last_name` AS c,`contact`.`staff`
AS d,`contact`.`ccode` AS e,`contact`.`first_name` AS
f,`contact`.`mat_ref` AS g,`contact`.`mat_no` AS h,'' AS i,3 AS j,2 AS
k,0 AS l,`contact`.`sysid` AS m,`contact`.`archive` AS
n,`contact`.`status` AS o,'' AS `rcode`,`contact`.`sysid` AS
`con_id`,`contact`.`mat_id`,`contact`.`private` FROM
`contact`,`relates` WHERE
lower(`contact`.`sysid`)=lower(`relates`.`sid`) AND
lower(`relates`.`mid`)=lower('EC4738F45B190490') AND
`relates`.`fieldno`=0 AND `contact`.`archive`=0

UNION SELECT 0 AS a,0 AS b,'' AS c,`matter`.`staff` AS
d,`matter`.`ccode` AS e,`matter`.`client` AS f,`matter`.`mat_ref` AS
g,`matter`.`mat_no` AS h,'' AS i,4 AS j,2 AS k,0 AS l,`matter`.`sysid`
AS m,`matter`.`archive` AS n,`matter`.`status` AS o,'' AS
`rcode`,`matter`.`con_id`,`matter`.`sysid` AS
`mat_id`,`matter`.`private` FROM `matter`,`relates` WHERE
lower(`matter`.`sysid`)=lower(`relates`.`sid`) AND
lower(`relates`.`mid`)=lower('EC4738F45B190490') AND
`relates`.`fieldno`=0 AND `matter`.`archive`=0

UNION SELECT `event`.`date` AS a,`event`.`time` AS b,`event`.`desc` AS
c,`event`.`staff` AS d,`event`.`ccode` AS e,`event`.`client` AS
f,`event`.`mat_ref` AS g,`event`.`mat_no` AS h,'' AS i,1 AS j,2 AS k,0
AS l,`event`.`sysid` AS m,`event`.`archive` AS n,`event`.`status` AS
o,'' AS `rcode`,`event`.`con_id`,`event`.`mat_id`,`event`.`private`
FROM `event`,`relates` WHERE
lower(`event`.`sysid`)=lower(`relates`.`mid`) AND
lower(`relates`.`sid`)=lower('EC4738F45B190490') AND
`relates`.`fieldno`<>0 AND `event`.`archive`=0

UNION SELECT `todo`.`date` AS a,0 AS b,`todo`.`desc` AS
c,`todo`.`staff` AS d,`todo`.`ccode` AS e,`todo`.`client` AS
f,`todo`.`mat_ref` AS g,`todo`.`mat_no` AS h,`todo`.`priority` AS i,2
AS j,2 AS k,0 AS l,`todo`.`sysid` AS m,`todo`.`archive` AS
n,`todo`.`status` AS o,'' AS
`rcode`,`todo`.`con_id`,`todo`.`mat_id`,`todo`.`private` FROM
`todo`,`relates` WHERE lower(`todo`.`sysid`)=lower(`relates`.`mid`)
AND lower(`relates`.`sid`)=lower('EC4738F45B190490') AND
`relates`.`fieldno`<>0 AND `todo`.`archive`=0

UNION SELECT 0 AS a,0 AS b,`contact`.`last_name` AS c,`contact`.`staff`
AS d,`contact`.`ccode` AS e,`contact`.`first_name` AS
f,`contact`.`mat_ref` AS g,`contact`.`mat_no` AS h,'' AS i,3 AS j,2 AS
k,0 AS l,`contact`.`sysid` AS m,`contact`.`archive` AS
n,`contact`.`status` AS o,'' AS `rcode`,`contact`.`sysid` AS
`con_id`,`contact`.`mat_id`,`contact`.`private` FROM
`contact`,`relates` WHERE
lower(`contact`.`sysid`)=lower(`relates`.`mid`) AND
lower(`relates`.`sid`)=lower('EC4738F45B190490') AND
`relates`.`fieldno`<>0 AND `contact`.`archive`=0

UNION SELECT 0 AS a,0 AS b,'' AS c,`matter`.`staff` AS
d,`matter`.`ccode` AS e,`matter`.`client` AS f,`matter`.`mat_ref` AS
g,`matter`.`mat_no` AS h,'' AS i,4 AS j,2 AS k,0 AS l,`matter`.`sysid`
AS m,`matter`.`archive` AS n,`matter`.`status` AS o,'' AS
`rcode`,`matter`.`con_id`,`matter`.`sysid` AS
`mat_id`,`matter`.`private` FROM `matter`,`relates` WHERE
lower(`matter`.`sysid`)=lower(`relates`.`mid`) AND
lower(`relates`.`sid`)=lower('EC4738F45B190490') AND
`relates`.`fieldno`<>0 AND `matter`.`archive`=0

ORDER BY j,n,a,b,c,g


------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread13784@lists.mysql.com
To unsubscribe, e-mail

Re: Union Statement Poor Performance

pop@logicbit.com writes:
> "How-To-Repeat:"
>
> hi,
>
> We have a rather large union statement that takes nearly 7 *minutes* to
> return on MySql, 5 seconds on MSQL and 5 seconds on Postgresql.
>
> The tables have around 5-10 thousand rows. To be fair the union
> statement had to be changed from using the IN() statement to basic sql
> syntax which kills performance on MSQL and Postgresql as well but only
> by 3-5 seconds whereas MySql takes over 5 *minutes*.
>
> Here is the Union stetment, I can provide you with the data files so
> you guys can run some test. I should mention that our product is in use
> by 10,000+ Including Lexis-Nexis, Toshiba and the DOD. The product
> "World Server" currently runs on everything from a mac, toshiba sg20
> appliance to blad servers using MSQL/Postgres on Linux/MS//Mac. We
> would like to include MySql as an option for our clients because the
> licensing is less expensive to the alternative Microsoft MSQL. Any
> feedback would be great.
>
> MySql 4.0.x gamma
> Mac OS X 1ghz 1gb of ram
>
> Frank A. Rivera -LOGICBit Corporation
> DATA.TXT Corporation www.timematters.com
>
> ----------------- How-To-Repeat:
>

[skip]

Hi!

MySQL is following SQL standard on UNION's, which means if UNION ALL
is not specified then all SELECT's automatically become distinct.

So, in order to make it provide same behaviour add ALL after any UNION
and wee the times.

If IN() that you have changed are from sub-selects, grab 4.1 sources
with Bitkeeper (description is in our manual) and try the same thing
with 4.1.


--
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Sinisa Milivojevic
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Fulltime Developer
/_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus
<___/ www.mysql.com

Join MySQL Users Conference and Expo:
http://www.mysql.com/events/uc2003/


------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread13785@lists.mysql.com
To unsubscribe, e-mail

Re: Union Statement Poor Performance

pop@logicbit.com wrote:
> "How-To-Repeat:"
>
> hi,
>
> We have a rather large union statement that takes nearly 7 *minutes* to
> return on MySql, 5 seconds on MSQL and 5 seconds on Postgresql.
>
> The tables have around 5-10 thousand rows. To be fair the union
> statement had to be changed from using the IN() statement to basic sql
> syntax which kills performance on MSQL and Postgresql as well but only
> by 3-5 seconds whereas MySql takes over 5 *minutes*.
>
> Here is the Union stetment, I can provide you with the data files so you
> guys can run some test. I should mention that our product is in use by
> 10,000+ Including Lexis-Nexis, Toshiba and the DOD. The product "World
> Server" currently runs on everything from a mac, toshiba sg20 appliance
> to blad servers using MSQL/Postgres on Linux/MS//Mac. We would like to
> include MySql as an option for our clients because the licensing is less
> expensive to the alternative Microsoft MSQL. Any feedback would be great.
>
> MySql 4.0.x gamma
> Mac OS X 1ghz 1gb of ram
>
> Frank A. Rivera -LOGICBit Corporation
> DATA.TXT Corporation www.timematters.com
>
> ----------------- How-To-Repeat:
>
> SELECT 0 AS a,0 AS b,'' AS c,`matter`.`staff` AS d,`matter`.`ccode` AS
> e,`matter`.`client` AS f,`matter`.`mat_ref` AS g,`matter`.`mat_no` AS
> h,'' AS i,4 AS j,1 AS k,0 AS l,`matter`.`sysid` AS m,`matter`.`archive`
> AS n,`matter`.`status` AS o,'' AS
> `rcode`,`matter`.`con_id`,`matter`.`sysid` AS
> `mat_id`,`matter`.`private` FROM `matter`,`contact` WHERE
> lower(`contact`.`sysid`)=lower('EC4738F45B190490') AND
> lower(`matter`.`sysid`)=lower(`contact`.`mat_id`)
>
> UNION SELECT 0 AS a,0 AS b,`contact`.`last_name` AS c,`contact`.`staff`
> AS d,`contact`.`ccode` AS e,`contact`.`first_name` AS
> f,`contact`.`mat_ref` AS g,`contact`.`mat_no` AS h,'' AS i,3 AS j,1 AS
> k,0 AS l,`contact`.`sysid` AS m,`contact`.`archive` AS
> n,`contact`.`status` AS o,'' AS `rcode`,`contact`.`sysid` AS
> `con_id`,`contact`.`mat_id`,`contact`.`private` FROM `contact`,`matter`
> WHERE lower(`matter`.`sysid`)=lower('EC4738F45B190490') AND
> lower(`contact`.`sysid`)=lower(`matter`.`con_id`)
>
> UNION SELECT `event`.`date` AS a,`event`.`time` AS b,`event`.`desc` AS
> c,`event`.`staff` AS d,`event`.`ccode` AS e,`event`.`client` AS
> f,`event`.`mat_ref` AS g,`event`.`mat_no` AS h,'' AS i,1 AS j,1 AS k,0
> AS l,`event`.`sysid` AS m,`event`.`archive` AS n,`event`.`status` AS
> o,'' AS `rcode`,`event`.`con_id`,`event`.`mat_id`,`event`.`private` FROM
> `event`,`relatea` WHERE lower(`event`.`sysid`)=lower(`relatea`.`mid`)
> AND lower(`relatea`.`sid`)=lower('C365834955124653') AND
> lower(`relatea`.`mid`)<>lower('EC4738F45B190490') OR
> lower(`relatea`.`sid`)=lower('A96FD8F4519C2915') AND
> lower(`relatea`.`mid`)<>lower('EC4738F45B190490') AND `event`.`archive`=0
>
> UNION SELECT `todo`.`date` AS a,0 AS b,`todo`.`desc` AS c,`todo`.`staff`
> AS d,`todo`.`ccode` AS e,`todo`.`client` AS f,`todo`.`mat_ref` AS
> g,`todo`.`mat_no` AS h,`todo`.`priority` AS i,2 AS j,1 AS k,0 AS
> l,`todo`.`sysid` AS m,`todo`.`archive` AS n,`todo`.`status` AS o,'' AS
> `rcode`,`todo`.`con_id`,`todo`.`mat_id`,`todo`.`private` FROM
> `todo`,`relatea` WHERE lower(`todo`.`sysid`)=lower(`relatea`.`mid`)
> AND lower(`relatea`.`sid`)=lower('C365834955124653') AND
> lower(`relatea`.`mid`)<>lower('EC4738F45B190490') OR
> lower(`relatea`.`sid`)=lower('A96FD8F4519C2915') AND
> lower(`relatea`.`mid`)<>lower('EC4738F45B190490') AND `todo`.`archive`=0
>
> UNION SELECT 0 AS a,0 AS b,`contact`.`last_name` AS c,`contact`.`staff`
> AS d,`contact`.`ccode` AS e,`contact`.`first_name` AS
> f,`contact`.`mat_ref` AS g,`contact`.`mat_no` AS h,'' AS i,3 AS j,1 AS
> k,0 AS l,`contact`.`sysid` AS m,`contact`.`archive` AS
> n,`contact`.`status` AS o,'' AS `rcode`,`contact`.`sysid` AS
> `con_id`,`contact`.`mat_id`,`contact`.`private` FROM `contact`,`relatea`
> WHERE lower(`contact`.`sysid`)=lower(`relatea`.`mid`) AND
> lower(`relatea`.`sid`)=lower('C365834955124653') AND
> lower(`relatea`.`mid`)<>lower('EC4738F45B190490') OR
> lower(`relatea`.`sid`)=lower('A96FD8F4519C2915') AND
> lower(`relatea`.`mid`)<>lower('EC4738F45B190490') AND
> `contact`.`archive`=0
>
> UNION SELECT 0 AS a,0 AS b,'' AS c,`matter`.`staff` AS
> d,`matter`.`ccode` AS e,`matter`.`client` AS f,`matter`.`mat_ref` AS
> g,`matter`.`mat_no` AS h,'' AS i,4 AS j,1 AS k,0 AS l,`matter`.`sysid`
> AS m,`matter`.`archive` AS n,`matter`.`status` AS o,'' AS
> `rcode`,`matter`.`con_id`,`matter`.`sysid` AS
> `mat_id`,`matter`.`private` FROM `matter`,`relatea` WHERE
> lower(`matter`.`sysid`)=lower(`relatea`.`mid`) AND
> lower(`relatea`.`sid`)=lower('C365834955124653') AND
> lower(`relatea`.`mid`)<>lower('EC4738F45B190490') OR
> lower(`relatea`.`sid`)=lower('A96FD8F4519C2915') AND
> lower(`relatea`.`mid`)<>lower('EC4738F45B190490') AND `matter`.`archive`=0
>
> UNION SELECT `event`.`date` AS a,`event`.`time` AS b,`event`.`desc` AS
> c,`event`.`staff` AS d,`event`.`ccode` AS e,`event`.`client` AS
> f,`event`.`mat_ref` AS g,`event`.`mat_no` AS h,'' AS i,1 AS j,2 AS k,0
> AS l,`event`.`sysid` AS m,`event`.`archive` AS n,`event`.`status` AS
> o,'' AS `rcode`,`event`.`con_id`,`event`.`mat_id`,`event`.`private` FROM
> `event`,`relates` WHERE lower(`event`.`sysid`)=lower(`relates`.`sid`)
> AND lower(`relates`.`mid`)=lower('EC4738F45B190490') AND
> `relates`.`fieldno`=0 AND `event`.`archive`=0
>
> UNION SELECT `todo`.`date` AS a,0 AS b,`todo`.`desc` AS c,`todo`.`staff`
> AS d,`todo`.`ccode` AS e,`todo`.`client` AS f,`todo`.`mat_ref` AS
> g,`todo`.`mat_no` AS h,`todo`.`priority` AS i,2 AS j,2 AS k,0 AS
> l,`todo`.`sysid` AS m,`todo`.`archive` AS n,`todo`.`status` AS o,'' AS
> `rcode`,`todo`.`con_id`,`todo`.`mat_id`,`todo`.`private` FROM
> `todo`,`relates` WHERE lower(`todo`.`sysid`)=lower(`relates`.`sid`)
> AND lower(`relates`.`mid`)=lower('EC4738F45B190490') AND
> `relates`.`fieldno`=0 AND `todo`.`archive`=0
>
> UNION SELECT 0 AS a,0 AS b,`contact`.`last_name` AS c,`contact`.`staff`
> AS d,`contact`.`ccode` AS e,`contact`.`first_name` AS
> f,`contact`.`mat_ref` AS g,`contact`.`mat_no` AS h,'' AS i,3 AS j,2 AS
> k,0 AS l,`contact`.`sysid` AS m,`contact`.`archive` AS
> n,`contact`.`status` AS o,'' AS `rcode`,`contact`.`sysid` AS
> `con_id`,`contact`.`mat_id`,`contact`.`private` FROM `contact`,`relates`
> WHERE lower(`contact`.`sysid`)=lower(`relates`.`sid`) AND
> lower(`relates`.`mid`)=lower('EC4738F45B190490') AND
> `relates`.`fieldno`=0 AND `contact`.`archive`=0
>
> UNION SELECT 0 AS a,0 AS b,'' AS c,`matter`.`staff` AS
> d,`matter`.`ccode` AS e,`matter`.`client` AS f,`matter`.`mat_ref` AS
> g,`matter`.`mat_no` AS h,'' AS i,4 AS j,2 AS k,0 AS l,`matter`.`sysid`
> AS m,`matter`.`archive` AS n,`matter`.`status` AS o,'' AS
> `rcode`,`matter`.`con_id`,`matter`.`sysid` AS
> `mat_id`,`matter`.`private` FROM `matter`,`relates` WHERE
> lower(`matter`.`sysid`)=lower(`relates`.`sid`) AND
> lower(`relates`.`mid`)=lower('EC4738F45B190490') AND
> `relates`.`fieldno`=0 AND `matter`.`archive`=0
>
> UNION SELECT `event`.`date` AS a,`event`.`time` AS b,`event`.`desc` AS
> c,`event`.`staff` AS d,`event`.`ccode` AS e,`event`.`client` AS
> f,`event`.`mat_ref` AS g,`event`.`mat_no` AS h,'' AS i,1 AS j,2 AS k,0
> AS l,`event`.`sysid` AS m,`event`.`archive` AS n,`event`.`status` AS
> o,'' AS `rcode`,`event`.`con_id`,`event`.`mat_id`,`event`.`private` FROM
> `event`,`relates` WHERE lower(`event`.`sysid`)=lower(`relates`.`mid`)
> AND lower(`relates`.`sid`)=lower('EC4738F45B190490') AND
> `relates`.`fieldno`<>0 AND `event`.`archive`=0
>
> UNION SELECT `todo`.`date` AS a,0 AS b,`todo`.`desc` AS c,`todo`.`staff`
> AS d,`todo`.`ccode` AS e,`todo`.`client` AS f,`todo`.`mat_ref` AS
> g,`todo`.`mat_no` AS h,`todo`.`priority` AS i,2 AS j,2 AS k,0 AS
> l,`todo`.`sysid` AS m,`todo`.`archive` AS n,`todo`.`status` AS o,'' AS
> `rcode`,`todo`.`con_id`,`todo`.`mat_id`,`todo`.`private` FROM
> `todo`,`relates` WHERE lower(`todo`.`sysid`)=lower(`relates`.`mid`)
> AND lower(`relates`.`sid`)=lower('EC4738F45B190490') AND
> `relates`.`fieldno`<>0 AND `todo`.`archive`=0
>
> UNION SELECT 0 AS a,0 AS b,`contact`.`last_name` AS c,`contact`.`staff`
> AS d,`contact`.`ccode` AS e,`contact`.`first_name` AS
> f,`contact`.`mat_ref` AS g,`contact`.`mat_no` AS h,'' AS i,3 AS j,2 AS
> k,0 AS l,`contact`.`sysid` AS m,`contact`.`archive` AS
> n,`contact`.`status` AS o,'' AS `rcode`,`contact`.`sysid` AS
> `con_id`,`contact`.`mat_id`,`contact`.`private` FROM `contact`,`relates`
> WHERE lower(`contact`.`sysid`)=lower(`relates`.`mid`) AND
> lower(`relates`.`sid`)=lower('EC4738F45B190490') AND
> `relates`.`fieldno`<>0 AND `contact`.`archive`=0
>
> UNION SELECT 0 AS a,0 AS b,'' AS c,`matter`.`staff` AS
> d,`matter`.`ccode` AS e,`matter`.`client` AS f,`matter`.`mat_ref` AS
> g,`matter`.`mat_no` AS h,'' AS i,4 AS j,2 AS k,0 AS l,`matter`.`sysid`
> AS m,`matter`.`archive` AS n,`matter`.`status` AS o,'' AS
> `rcode`,`matter`.`con_id`,`matter`.`sysid` AS
> `mat_id`,`matter`.`private` FROM `matter`,`relates` WHERE
> lower(`matter`.`sysid`)=lower(`relates`.`mid`) AND
> lower(`relates`.`sid`)=lower('EC4738F45B190490') AND
> `relates`.`fieldno`<>0 AND `matter`.`archive`=0
>
> ORDER BY j,n,a,b,c,g
>
>
> ------------------------------------------------------------ ---------
> Before posting, please check:
> http://www.mysql.com/manual.php (the manual)
> http://lists.mysql.com/ (the list archive)
>
> To request this thread, e-mail bugs-thread13784@lists.mysql.com
> To unsubscribe, e-mail
>
>
>


--
MySQL Conference and Expo 2003 http://www.mysql.com/events/uc2003/index.html
For technical support contracts, visit https://order.mysql.com/?ref=msal
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Alexander Keremidarski
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer
/_/ /_/\_, /___/\___\_\___/ Sofia, Bulgaria
<___/ www.mysql.com




------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread13786@lists.mysql.com
To unsubscribe, e-mail

Re: Union Statement Poor Performance

Hi,

pop@logicbit.com wrote:
> "How-To-Repeat:"


> SELECT 0 AS a,0 AS b,'' AS c,`matter`.`staff` AS d,`matter`.`ccode` AS
> e,`matter`.`client` AS f,`matter`.`mat_ref` AS g,`matter`.`mat_no` AS
> h,'' AS i,4 AS j,1 AS k,0 AS l,`matter`.`sysid` AS m,`matter`.`archive`
> AS n,`matter`.`status` AS o,'' AS
> `rcode`,`matter`.`con_id`,`matter`.`sysid` AS
> `mat_id`,`matter`.`private` FROM `matter`,`contact` WHERE
> lower(`contact`.`sysid`)=lower('EC4738F45B190490') AND
> lower(`matter`.`sysid`)=lower(`contact`.`mat_id`)



WHERE lower(`contact`.`sysid`)=lower('EC4738F45B190490') AND
^^^^^^^^^^^^^^^^^^^^^^^^^

1. Such where clause effectively prevents any index usage - query will always need
to scan all rows to evaluate result of comparison.

2. Function LOWER() accepts single parameter so your query will fail under MySQL
with Syntax error

Probably you mean:

WHERE LOWER(CONCAT(`contact`, `sysid`))=lower('EC4738F45B190490')


3. By default all string comparsions in SQL are case insensitive so it never makes
sense to check if lower(a) = lower(b)

mysql> select 'Hello' = 'hello', 'HELLO' = 'HeLlO';

returns 1, 1 i.e. true, true


In order to speed up such query with proper indexes above part of where clause can
be rewritten as:

WHERE contact = 'EC' AND sysid = '4738F45B190490'

.... and so on ...



--
MySQL Conference and Expo 2003 http://www.mysql.com/events/uc2003/index.html
For technical support contracts, visit https://order.mysql.com/?ref=msal
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Alexander Keremidarski
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer
/_/ /_/\_, /___/\___\_\___/ Sofia, Bulgaria
<___/ www.mysql.com




------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread13787@lists.mysql.com
To unsubscribe, e-mail

Re: Union Statement Poor Performance

Frank,

Alexander Keremidarski wrote:
> Hi,


> 2. Function LOWER() accepts single parameter so your query will fail
> under MySQL with Syntax error
>
> Probably you mean:
>
> WHERE LOWER(CONCAT(`contact`, `sysid`))=lower('EC4738F45B190490')

Sorry about this one. Looks like I must change font so I can see the difference
between . and , :)


Other notes are still valid.


One more question.
You said:

MySql 4.0.x gamma

Which 4.0.x? All of them up to 4.0.10 or only some?

Best regards

--
MySQL Conference and Expo 2003 http://www.mysql.com/events/uc2003/index.html
For technical support contracts, visit https://order.mysql.com/?ref=msal
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Alexander Keremidarski
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer
/_/ /_/\_, /___/\___\_\___/ Sofia, Bulgaria
<___/ www.mysql.com




------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread13788@lists.mysql.com
To unsubscribe, e-mail