CODEWITHSUNDEEP
mysqljoinindexing
Hadi Aminzadeh
Hadi Aminzadeh

Reputation: 95

MySQL does not use index in table join

I have 2 table, product and product_pic with 100K rows.

I have index on porduct_id on product_pic table but mysql dose not use index on joining them!

CREATE TABLE `product` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(250) CHARACTER SET utf8 NOT NULL DEFAULT '',
  `type` int(11) NOT NULL,
  `price` varchar(16) CHARACTER SET utf8 NOT NULL DEFAULT '',
  `keyword` text CHARACTER SET utf8 NOT NULL,
  `summary` text COLLATE utf8_general_ci NOT NULL,
  `note` text CHARACTER SET utf8 NOT NULL,
  `catid` int(11) NOT NULL DEFAULT 0,
  `date` date NOT NULL DEFAULT '0000-00-00',
  `expdate` date NOT NULL DEFAULT '0000-00-00',
  `show` int(11) NOT NULL DEFAULT 0,
  `hit` int(11) NOT NULL,
  `userid` int(11) NOT NULL DEFAULT 0,
  `usertype` tinyint(4) NOT NULL DEFAULT 0,
  `rand` int(11) NOT NULL DEFAULT 0,
  `provinceid` tinyint(4) NOT NULL,
  `timestamp` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `active` tinyint(4) NOT NULL,
  `expdate_email` tinyint(4) NOT NULL,
  `active_time` datetime NOT NULL,
  `update_time` datetime NOT NULL,
  `weight` float NOT NULL,
  `ucategory` int(11) NOT NULL DEFAULT -1,
  `searchstatus` tinyint(1) NOT NULL DEFAULT 0,
  `vitreen` tinyint(4) NOT NULL DEFAULT 1,
  `summery` varchar(256) COLLATE utf8_general_ci NOT NULL,
  `notification` varchar(255) COLLATE utf8_general_ci NOT NULL,
  `active_buy` tinyint(4) NOT NULL DEFAULT 1,
  `autotranslate` tinyint(1) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  KEY `usertype` (`usertype`),
  KEY `catid` (`catid`),
  KEY `active` (`active`),
  KEY `type` (`type`),
  KEY `userid` (`userid`),
  KEY `update_time` (`update_time`),
  FULLTEXT KEY `title` (`title`),
  FULLTEXT KEY `note` (`note`,`keyword`,`title`)
) ENGINE=InnoDB AUTO_INCREMENT=42987 DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci ROW_FORMAT=DYNAMIC

product pic:

 CREATE TABLE `product_pic` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `userid` int(11) NOT NULL DEFAULT 0,
  `productid` int(11) NOT NULL DEFAULT 0,
  `pic` varchar(100) COLLATE utf8_general_ci NOT NULL DEFAULT '',
  `date` date NOT NULL DEFAULT '0000-00-00',
  `default_img` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `userid` (`userid`,`productid`),
  KEY `productid` (`productid`)
) ENGINE=InnoDB AUTO_INCREMENT=48020 DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci

I try to use hint but nothing happend!

our query:

SELECT p.`id`, p.`title` name, product_pic.`pic` 
FROM `product` p
LEFT JOIN `product_pic` USE INDEX (productid)  ON p.`id`=product_pic.`productid`
where p.id in (SELECT * FROM `PID`) GROUP BY p.`id`;

Explain output:

+------+-------------+-------------+--------+---------------+---------+---------+------+------+-------------------------------------------------+
| id   | select_type | table       | type   | possible_keys | key     | key_len | ref  | rows | Extra                                           |
+------+-------------+-------------+--------+---------------+---------+---------+------+------+-------------------------------------------------+
|    1 | PRIMARY     | p           | ALL    | PRIMARY       | NULL    | NULL    | NULL |    1 | Using temporary; Using filesort                 |
|    1 | PRIMARY     | product_pic | ALL    | productid     | NULL    | NULL    | NULL |    1 | Using where; Using join buffer (flat, BNL join) |
|    1 | PRIMARY     | PID         | eq_ref | PRIMARY       | PRIMARY | 4       | p.id |    1 | Using index                                     |
+------+-------------+-------------+--------+---------------+---------+---------+------+------+-------------------------------------------------+

our query take 3 min for 36 row !!

36 rows in set (3 min 27.481 sec)

Upvotes: 0

Views: 52

Answers (1)

Rick James
Rick James

Reputation: 142298

  • Is there at most 1 pic per product? If so, you don't need the GROUP BY.

  • If there are multiple pics per product, then the GROUP BY is improperly used -- after the GROUP BY, which product_pic.pic do you want it to show?

  • where p.id in (SELECT * FROM PID) --> JOIN PID USING(id)

  • The EXPLAIN uses bogus -- it says ALL, but Rows=1, Is there only one row in each table??

  • Independent of other changes, change KEY (productid) to KEY(productid, pic).

  • Avoid index hints.

Upvotes: 1

Related Questions