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 |
| |
| 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) | | | |
|
| shortdesc | text | | | |
|
| longdesc | text | | | |
|
| compatibility | varchar(100) | | MUL | |
|
| 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 | |
±----------±-----------±-----±----±--------±------+