I have two slave mysql db (5.5.27) (both are running on different machine with same OS).
There is three table (CATEGORY_TREE, SEO_METADATA, TAGS).
There schema definition are as follow:
| CATEGORY_TREE | CREATE TABLE `CATEGORY_TREE` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`child_cid` int(11) DEFAULT NULL,
`parent_cid` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `child_cid` (`child_cid`,`parent_cid`)
) ENGINE=InnoDB AUTO_INCREMENT=9528 DEFAULT CHARSET=latin1 |
| TAGS | CREATE TABLE `TAGS` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`name` varchar(64) NOT NULL,
`owner` varchar(20) DEFAULT NULL,
`type` varchar(20) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `id` (`id`,`name`,`owner`,`type`)
) ENGINE=InnoDB AUTO_INCREMENT=165498 DEFAULT CHARSET=latin1 |
| SEO_METADATA | CREATE TABLE `SEO_METADATA` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`canonicalString` varchar(128) DEFAULT NULL,
`dataId` varchar(20) DEFAULT NULL,
`oldId` int(30) DEFAULT NULL,
`type` varchar(1) DEFAULT NULL,
`canonicalString` varchar(128) DEFAULT NULL,
`searchString` text,
PRIMARY KEY (`id`),
KEY `id_type` (`dataId`,`type`),
KEY `oldid_type` (`oldId`,`type`),
KEY `canonicalstringidx` (`canonicalString`)
) ENGINE=InnoDB AUTO_INCREMENT=3863159 DEFAULT CHARSET=latin1 |
On running "EXPLAIN" for Select db query, I am getting different output
explain SELECT ct.child_cid AS 'tid', ct.parent_cid AS 'ptid', t.name, sm.canonicalString FROM CATEGORY_TREE ct, TAGS t, SEO_METADATA sm WHERE ct.child_cid = t.id AND sm.dataId = cast(ct.child_cid as char) AND sm.type = 't' AND (sm.searchString IS NULL OR sm.searchString = '') GROUP BY ct.child_cid LIMIT 10000;
**Machine 1**
+----+-------------+-------+--------+---------------+-----------+---------+------------------------+---------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+---------------+-----------+---------+------------------------+---------+----------------------------------------------+
| 1 | SIMPLE | ct | index | child_cid | child_cid | 10 | NULL | 2264 | Using index; Using temporary; Using filesort |
| 1 | SIMPLE | t | eq_ref | PRIMARY,id | PRIMARY | 8 | cms.ct.child_cid | 1 | Using where |
| 1 | SIMPLE | sm | ALL | NULL | NULL | NULL | NULL | 1588507 | Using where; Using join buffer |
+----+-------------+-------+--------+---------------+-----------+---------+------------------------+---------+----------------------------------------------+
**Machine 2**
+----+-------------+-------+--------+---------------+-----------+---------+------------------------+---------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+---------------+-----------+---------+------------------------+---------+----------------------------------------------+
| 1 | SIMPLE | sm | ALL | NULL | NULL | NULL | NULL | 1524208 | Using where; Using temporary; Using filesort |
| 1 | SIMPLE | ct | index | child_cid | child_cid | 10 | NULL | 2500 | Using where; Using index; Using join buffer |
| 1 | SIMPLE | t | eq_ref | PRIMARY,id | PRIMARY | 8 | cms.ct.child_cid | 1 | Using where |
+----+-------------+-------+--------+---------------+-----------+---------+------------------------+---------+----------------------------------------------+
In one it is using "index" first, but in other it is not.
I have gone through "Why does the same exact query produce 2 different MySQL explain results?", and checked all the parameter mentioned by @spencer7593.
Apart from this, I have run "optimize" table command in Machine 2 DB, but there no change in output.
I know that mysql can be "forced" to use index, but want to know the root cause for same.
Few post have mentioned that Innodb buffer-size can also be one reason for different output. In my case, both are almost same. Below is output of "SHOW ENGINE INNODB STATUS" from both db.
**Machine 1**
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 10989076480; in additional pool allocated 0
Dictionary memory allocated 5959814
Buffer pool size 655360
Free buffers 0
Database pages 645758
**Machine 2**
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 10989076480; in additional pool allocated 0
Dictionary memory allocated 5659318
Buffer pool size 655359
Free buffers 0
Database pages 645245
Thanks
