CODEWITHSUNDEEP
mysql
JezB
JezB

Reputation: 528

MySQL Get rows that only exist in one table performance

The basics of this query have been asked, and answered, many times before, but I'm still having trouble with performance. Here are the details:

I have the table, Products, that has 105724 rows. I have an update table, _e360products, that has 51813 rows. I am matching on an alphanumeric 10 character code, that is indexed (unique) on both tables.

I have tried:

SELECT _e360products.Product_Code, products.StockCode
FROM _e360products Left Join Products ON _e360products.Product_Code = Products.StockCode
WHERE products.StockCode IS NULL

and:

SELECT Product_Code 
FROM _e360products
WHERE Product_code NOT IN (SELECT StockCode FROM Products)

and, just for a laugh, even:

SELECT Product_Code
FROM _e360products 
WHERE (SELECT count(*) FROM Products WHERE StockCode = Product_code) = 0

None of these have returned results within 20 mins!

If I reverse the queries, i.e. getting unique rows from _e360products, I get results very quickly.

Does anyone have any ideas?

~~~~~ Update ~~~~~ Explain results are:

+----+-------------+---------------+--------+---------------+--------------+---------+------------------------------------------+-------+--------------------------------------+
| id | select_type | table         | type   | possible_keys | key          | key_len | ref                                      | rows  | Extra                                |
+----+-------------+---------------+--------+---------------+--------------+---------+------------------------------------------+-------+--------------------------------------+
|  1 | SIMPLE      | _e360products | index  | NULL          | Product_code | 12      | NULL                                     | 50811 | Using index                          |
|  1 | SIMPLE      | Products      | eq_ref | stockcode     | stockcode    | 12      | plumbase_bkup._e360products.Product_code |     1 | Using where; Using index; Not exists |
+----+-------------+---------------+--------+---------------+--------------+---------+------------------------------------------+-------+--------------------------------------+

CREATE TABLE `_e360products` (
  `Product_code` varchar(10) CHARACTER SET latin1 NOT NULL DEFAULT '',
  `Manufacturers_code` varchar(255) DEFAULT '',
  `Description` varchar(255) DEFAULT '',
  `Supplier` varchar(255) DEFAULT '',
  `Price` varchar(20) DEFAULT '',
  `VAT` varchar(20) DEFAULT '',
  `Analysis_code` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`Product_code`),
  KEY `Product_code` (`Product_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `products` (
  `productid` int(11) NOT NULL AUTO_INCREMENT,
  `QPUM2` varchar(50) NOT NULL DEFAULT '1',
  `NWID` varchar(50) NOT NULL DEFAULT '0',
  `NHEI` varchar(50) NOT NULL DEFAULT '0',
  `NLEN` varchar(50) NOT NULL DEFAULT '0',
  `donotdisplayprice` tinyint(2) DEFAULT '0',
  `productname` text,
  `stockcode` varchar(10) NOT NULL DEFAULT '',
  `analysiscode` varchar(50) DEFAULT '',
  `usestockcontrol` int(11) DEFAULT '0',
  `stockvalue` int(11) DEFAULT '0',
  `stock_notification_level` int(11) DEFAULT '0',
  `sectionid` int(11) DEFAULT '0',
  `productprice` varchar(50) DEFAULT '',
  `productprice_incvat` varchar(50) DEFAULT '',
  `deleted` int(11) DEFAULT '0',
  PRIMARY KEY (`productid`),
  UNIQUE KEY `stockcode` (`stockcode`) USING BTREE,
  KEY `deleted` (`deleted`),
  KEY `allowordering` (`allowordering`),
) ENGINE=MyISAM AUTO_INCREMENT=147440 DEFAULT CHARSET=latin1;

NoteL Products table doesn't include ALL the fields, as there are quite a few...

Upvotes: 0

Views: 102

Answers (2)

Mariusz Sakowski
Mariusz Sakowski

Reputation: 3280

Please provide a query execution plan (EXPLAIN), it seems your index is not used. Also show as CREATE TABLEs for both tables.

Upvotes: 1

rauschen
rauschen

Reputation: 3996

Typo? StockCode[add space here]IS

SELECT _e360products.Product_Code, products.StockCode
FROM _e360products Left Join Products ON _e360products.Product_Code = Products.StockCode
WHERE products.StockCode IS NULL

Upvotes: 1

Related Questions