MySQL performance question on SELECT and JOIN. experts only

am 23.12.2006 19:56:23 von Andrew S

Re: MySQL performance question on SELECT and JOIN. experts only

am 24.12.2006 00:21:42 von Bill Karwin

Andrew S wrote:
> - when I ran the statement
> select productname from productdetailentbl left join producttbl on
> productdetailentbl.productid=producttbl.productid left join pricetblN
> on productdetailentbl.productid=pricetblN.productid where vendorpartno
> like '%EMOTE15%' OR concat(productname,compatibility) like '%REMOTE15%'
> limit 25;
> Empty set (6.49 sec)
>
> if you notice, I only change the "AND" to "OR". Why this is happening?

I have been told that MySQL doesn't optimize OR expressions very well.
I'm not sure I understand the reason well enough to explain it. You
might search www.mysqlperformanceblog.com for some good tips.

Also, using LIKE in the way you are doing, with a wildcard at the start
of the value, prevents the query from using an index. That's bound to
cause slow performance.

Regards,
Bill K.

Re: MySQL performance question on SELECT and JOIN. experts only

am 24.12.2006 00:25:44 von Bill Karwin

> Andrew S wrote:
>> if you notice, I only change the "AND" to "OR". Why this is happening?

Another tip... This form of query:

SELECT ...
WHERE condition1 OR condition2

Is equivalent to:

SELECT ...
WHERE condition1
UNION ALL
SELECT ...
WHERE condition2

The latter may perform a lot better, especially prior to MySQL 5.0.

Regards,
Bill K.

Re: MySQL performance question on SELECT and JOIN. experts only

am 24.12.2006 19:35:56 von Bill Karwin

>> Andrew S wrote:
> Bill, I wanted to retrive different cols from different tables, so
> UNION does not really work for this case. Is there any way else?

I'm not sure I understand why the solution does not work for you. The
solution works if each "side" of UNION contains a JOIN.

Regards,
Bill K.

Re: MySQL performance question on SELECT and JOIN. experts only

am 26.12.2006 18:19:38 von shakah

On Dec 23, 6:25 pm, Bill Karwin wrote:
> > Andrew S wrote:
> >> if you notice, I only change the "AND" to "OR". Why this is happening?Another tip... This form of query:
>
> SELECT ...
> WHERE condition1 OR condition2
>
> Is equivalent to:
>
> SELECT ...
> WHERE condition1
> UNION ALL
> SELECT ...
> WHERE condition2
>
> The latter may perform a lot better, especially prior to MySQL 5.0.
>
> Regards,
> Bill K.

Don't you mean "UNION DISTINCT"?

BEGIN;

CREATE TABLE mytest (
the_char char(1) NOT NULL
) ;

INSERT INTO mytest VALUES('A') ;
INSERT INTO mytest VALUES('B') ;
INSERT INTO mytest VALUES('C') ;
INSERT INTO mytest VALUES('D') ;
INSERT INTO mytest VALUES('E') ;
INSERT INTO mytest VALUES('F') ;
INSERT INTO mytest VALUES('G') ;

-- ...query using OR
SELECT the_char
FROM mytest
WHERE (the_char='A')
OR (the_char IN ('A','B')) ;

the_char
----------
A
B
(2 rows)

-- ...supposed equivalent using UNION ALL
SELECT the_char
FROM mytest
WHERE the_char='A'
UNION ALL
SELECT the_char
FROM mytest
WHERE the_char IN ('A','B') ;

the_char
----------
A
A
B
(3 rows)

-- ...actual equivalent, using UNION DISTINCT ?
SELECT the_char
FROM mytest
WHERE the_char='A'
UNION DISTINCT
SELECT the_char
FROM mytest
WHERE the_char IN ('A','B') ;

the_char
----------
A
B
(2 rows)