MySQL performance question on SELECT and JOIN. experts only

[Andrew S]

Hello Mr. Expert:
- I have 3 tables in mysql in MyISAM table format, I am using mysql4.0
on freebsd5.3
- producttbl, productdetailentbl, pricetblN
- they all have "productid" as the Primary KEY. there are less than
300,000 records in each table
- 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%' AND concat(productname,compatibility) like
'%REMOTE15%' limit 25;
Empty set (0.79 sec)
pretty fast
- 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?
Is there any way to optimize the query?
following are table structures for these 3 tables:

mysql> desc producttbl;
+-------------------+---------------------+------+-----+---------------------+----------------+
| Field | Type | Null | Key | Default
| Extra |
+-------------------+---------------------+------+-----+---------------------+----------------+
| productid | int(10) unsigned | | PRI | NULL
| auto_increment |
| basic_catID | int(10) unsigned | | | 0
| |
| main_catID | int(10) unsigned | | | 0
| |
| sub_catID | int(10) unsigned | | | 0
| |
| producttype | char(1) | | | P
| |
| vendor | varchar(64) | | MUL | <none>
| |
| vendorpartno | varchar(50) | | MUL |
| |
| createtime | datetime | | | 0000-00-00
00:00:00 | |
| supplierid | int(10) unsigned | YES | | 0
| |
| supplierpartno | varchar(30) | YES | |
| |
| cost | float(7,2) unsigned | YES | | 0.00
| |
| price | float(7,2) | | | 0.00
| |
| stocklevel | int(10) unsigned | YES | | 0
| |
| availableQuantity | int(10) | | | 0
| |
| soldQuantity | int(10) unsigned | | | 0
| |
| lastupdatetime | datetime | | | 0000-00-00
00:00:00 | |
| costupdatetime | datetime | | | 0000-00-00
00:00:00 | |
| banned | char(1) | | | N
| |
| specialPrice | float(7,2) unsigned | | | 0.00
| |
| onPromotion | char(1) | | | N
| |
| onSpecial | char(1) | | | N
| |
| onRebate | char(1) | | | N
| |
| rebateValue | float(7,2) | | | 0.00
| |
| rebateExpireDate | date | | | 0000-00-00
| |
| specialExpireDate | date | | | 0000-00-00
| |
| extravpnid | varchar(128) | | MUL |
| |
| lockContent | char(1) | | | N
| |
+-------------------+---------------------+------+-----+---------------------+----------------+

mysql> desc productdetailentbl;
+---------------------+------------------+------+-----+-----------+-------+
| Field | Type | Null | Key | Default |
Extra |
+---------------------+------------------+------+-----+-----------+-------+
| productid | int(10) unsigned | | PRI | 0 |
|
| productname | varchar(100) | | MUL | |
|
| brand | varchar(64) | | | <none> |
|
| shortdesc | text | | | |
|
| longdesc | text | | | |
|
| compatibility | varchar(100) | | MUL | <none> |
|
| requirement | text | | | |
|
| specifications | text | | | |
|
| keyfeatures | text | | | |
|
| includes | text | | | |
|
| imagefile | varchar(64) | | | nopic.gif |
|
| largeimagefile | varchar(64) | | | nopic.gif |
|
| promotionname | varchar(100) | | | |
|
| promotionshortdesc | text | | | |
|
| productCouponFile | varchar(64) | | | nopic.gif |
|
| productBrochureFile | varchar(64) | | | nopic.gif |
|
+---------------------+------------------+------+-----+-----------+-------+

mysql> desc pricetblN;
+-----------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+------------+------+-----+---------+-------+
| productid | int(10) | | PRI | 0 | |
| price | float(7,2) | YES | | NULL | |
+-----------+------------+------+-----+---------+-------+

[Bill Karwin]

Post by Andrew S
- 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.

[Bill Karwin]

Post by Andrew S
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.

[Bill Karwin]

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.

[s***@gmail.com]

Post by Bill Karwin
SELECT ...
WHERE condition1 OR condition2
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)