Description:
i have a 1 master/2-slave database running 5.6.17 and observing a strange behavior.
for one query the master and 2 slaves is using right plan(using index), whereas the other one doing full table scan.
Here is the info.
MASTER:
mysql> EXPLAIN SELECT cont.itemid FROM th_packages as pac JOIN th_packagecontents as cont ON pac.packageid = cont.packageid WHERE pac.tradeid IN (23994748651,23915474815,24001315459,23955808891,23955965455,23970035647,23992571563,24001376803,23915465071,23965032871,23994282571) AND pac.packagetype IN (3,4,5)\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: pac
type: range
possible_keys: PRIMARY,th_packages_tradeid
key: th_packages_tradeid
key_len: 8
ref: NULL
rows: 11
Extra: Using index condition; Using where
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: cont
type: ref
possible_keys: th_packagecontents_packageid
key: th_packagecontents_packageid
key_len: 8
ref: lt1_tradehouse_trade02.pac.packageid
rows: 1
Extra: NULL
2 rows in set (0.00 sec)
Slave 261:
mysql> EXPLAIN SELECT cont.itemid FROM th_packages as pac JOIN th_packagecontents as cont ON pac.packageid = cont.packageid WHERE pac.tradeid IN (23994748651,23915474815,24001315459,23955808891,23955965455,23970035647,23992571563,24001376803,23915465071,23965032871,23994282571) AND pac.packagetype IN (3,4,5)\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: pac
type: range
possible_keys: PRIMARY,th_packages_tradeid
key: th_packages_tradeid
key_len: 8
ref: NULL
rows: 11
Extra: Using where
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: cont
type: ref
possible_keys: th_packagecontents_packageid
key: th_packagecontents_packageid
key_len: 8
ref: lt1_tradehouse_trade02.pac.packageid
rows: 1
Extra:
2 rows in set (0.00 sec)
Slave 200:
mysql> EXPLAIN SELECT cont.itemid FROM th_packages as pac JOIN th_packagecontents as cont ON pac.packageid = cont.packageid WHERE pac.tradeid IN (23994748651,23915474815,24001315459,23955808891,23955965455,23970035647,23992571563,24001376803,23915465071,23965032871,23994282571) AND pac.packagetype IN (3,4,5)\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: pac
type: range
possible_keys: PRIMARY,th_packages_tradeid
key: th_packages_tradeid
key_len: 8
ref: NULL
rows: 11
Extra: Using index condition; Using where
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: cont
type: ref
possible_keys: th_packagecontents_packageid
key: th_packagecontents_packageid
key_len: 8
ref: lt1_tradehouse_trade02.pac.packageid
rows: 1
Extra: NULL
Slave 198:
mysql> EXPLAIN SELECT cont.itemid FROM th_packages as pac JOIN th_packagecontents as cont ON pac.packageid = cont.packageid WHERE pac.tradeid IN (23994748651,23915474815,24001315459,23955808891,23955965455,23970035647,23992571563,24001376803,23915465071,23965032871,23994282571) AND pac.packagetype IN (3,4,5)\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: pac
type: ALL
possible_keys: PRIMARY,th_packages_tradeid
key: NULL
key_len: NULL
ref: NULL
rows: 1501837007
Extra: Using where
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: cont
type: ref
possible_keys: th_packagecontents_packageid
key: th_packagecontents_packageid
key_len: 8
ref: lt1_tradehouse_trade02.pac.packageid
rows: 1
Extra: NULL
2 rows in set (0.00 sec)
Also ran this query and the cardinality of the indexed column in problem table shows a big difference. Could this be the cause.
Slave 198:
mysql> select * from STATISTICS where TABLE_SCHEMA='lt1_tradehouse_trade02' and table_name='th_packages' and column_name in ('tradeid','packageid','packagetype')\G
*************************** 1. row ***************************
TABLE_CATALOG: def
TABLE_SCHEMA: lt1_tradehouse_trade02
TABLE_NAME: th_packages
NON_UNIQUE: 0
INDEX_SCHEMA: lt1_tradehouse_trade02
INDEX_NAME: PRIMARY
SEQ_IN_INDEX: 1
COLUMN_NAME: packageid
COLLATION: A
CARDINALITY: 1501916946
SUB_PART: NULL
PACKED: NULL
NULLABLE:
INDEX_TYPE: BTREE
COMMENT:
INDEX_COMMENT:
*************************** 2. row ***************************
TABLE_CATALOG: def
TABLE_SCHEMA: lt1_tradehouse_trade02
TABLE_NAME: th_packages
NON_UNIQUE: 1
INDEX_SCHEMA: lt1_tradehouse_trade02
INDEX_NAME: th_packages_tradeid
SEQ_IN_INDEX: 1
COLUMN_NAME: tradeid
COLLATION: A
CARDINALITY: 2
SUB_PART: NULL
PACKED: NULL
NULLABLE:
INDEX_TYPE: BTREE
COMMENT:
INDEX_COMMENT:
Slave 261:
mysql> select * from STATISTICS where TABLE_SCHEMA='lt1_tradehouse_trade02' and table_name='th_packages' and column_name in ('tradeid','packageid','packagetype')\G
*************************** 1. row ***************************
TABLE_CATALOG: def
TABLE_SCHEMA: lt1_tradehouse_trade02
TABLE_NAME: th_packages
NON_UNIQUE: 0
INDEX_SCHEMA: lt1_tradehouse_trade02
INDEX_NAME: PRIMARY
SEQ_IN_INDEX: 1
COLUMN_NAME: packageid
COLLATION: A
CARDINALITY: 1249288359
SUB_PART: NULL
PACKED: NULL
NULLABLE:
INDEX_TYPE: BTREE
COMMENT:
INDEX_COMMENT:
*************************** 2. row ***************************
TABLE_CATALOG: def
TABLE_SCHEMA: lt1_tradehouse_trade02
TABLE_NAME: th_packages
NON_UNIQUE: 1
INDEX_SCHEMA: lt1_tradehouse_trade02
INDEX_NAME: th_packages_tradeid
SEQ_IN_INDEX: 1
COLUMN_NAME: tradeid
COLLATION: A
CARDINALITY: 1249288359
SUB_PART: NULL
PACKED: NULL
NULLABLE:
INDEX_TYPE: BTREE
COMMENT:
INDEX_COMMENT:
How to repeat:
I am not sure how o repeat this behavior, but this is happening for some other server also which has this schema. After I do analyze table, it appears again after some time.
Description: i have a 1 master/2-slave database running 5.6.17 and observing a strange behavior. for one query the master and 2 slaves is using right plan(using index), whereas the other one doing full table scan. Here is the info. MASTER: mysql> EXPLAIN SELECT cont.itemid FROM th_packages as pac JOIN th_packagecontents as cont ON pac.packageid = cont.packageid WHERE pac.tradeid IN (23994748651,23915474815,24001315459,23955808891,23955965455,23970035647,23992571563,24001376803,23915465071,23965032871,23994282571) AND pac.packagetype IN (3,4,5)\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: pac type: range possible_keys: PRIMARY,th_packages_tradeid key: th_packages_tradeid key_len: 8 ref: NULL rows: 11 Extra: Using index condition; Using where *************************** 2. row *************************** id: 1 select_type: SIMPLE table: cont type: ref possible_keys: th_packagecontents_packageid key: th_packagecontents_packageid key_len: 8 ref: lt1_tradehouse_trade02.pac.packageid rows: 1 Extra: NULL 2 rows in set (0.00 sec) Slave 261: mysql> EXPLAIN SELECT cont.itemid FROM th_packages as pac JOIN th_packagecontents as cont ON pac.packageid = cont.packageid WHERE pac.tradeid IN (23994748651,23915474815,24001315459,23955808891,23955965455,23970035647,23992571563,24001376803,23915465071,23965032871,23994282571) AND pac.packagetype IN (3,4,5)\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: pac type: range possible_keys: PRIMARY,th_packages_tradeid key: th_packages_tradeid key_len: 8 ref: NULL rows: 11 Extra: Using where *************************** 2. row *************************** id: 1 select_type: SIMPLE table: cont type: ref possible_keys: th_packagecontents_packageid key: th_packagecontents_packageid key_len: 8 ref: lt1_tradehouse_trade02.pac.packageid rows: 1 Extra: 2 rows in set (0.00 sec) Slave 200: mysql> EXPLAIN SELECT cont.itemid FROM th_packages as pac JOIN th_packagecontents as cont ON pac.packageid = cont.packageid WHERE pac.tradeid IN (23994748651,23915474815,24001315459,23955808891,23955965455,23970035647,23992571563,24001376803,23915465071,23965032871,23994282571) AND pac.packagetype IN (3,4,5)\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: pac type: range possible_keys: PRIMARY,th_packages_tradeid key: th_packages_tradeid key_len: 8 ref: NULL rows: 11 Extra: Using index condition; Using where *************************** 2. row *************************** id: 1 select_type: SIMPLE table: cont type: ref possible_keys: th_packagecontents_packageid key: th_packagecontents_packageid key_len: 8 ref: lt1_tradehouse_trade02.pac.packageid rows: 1 Extra: NULL Slave 198: mysql> EXPLAIN SELECT cont.itemid FROM th_packages as pac JOIN th_packagecontents as cont ON pac.packageid = cont.packageid WHERE pac.tradeid IN (23994748651,23915474815,24001315459,23955808891,23955965455,23970035647,23992571563,24001376803,23915465071,23965032871,23994282571) AND pac.packagetype IN (3,4,5)\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: pac type: ALL possible_keys: PRIMARY,th_packages_tradeid key: NULL key_len: NULL ref: NULL rows: 1501837007 Extra: Using where *************************** 2. row *************************** id: 1 select_type: SIMPLE table: cont type: ref possible_keys: th_packagecontents_packageid key: th_packagecontents_packageid key_len: 8 ref: lt1_tradehouse_trade02.pac.packageid rows: 1 Extra: NULL 2 rows in set (0.00 sec) Also ran this query and the cardinality of the indexed column in problem table shows a big difference. Could this be the cause. Slave 198: mysql> select * from STATISTICS where TABLE_SCHEMA='lt1_tradehouse_trade02' and table_name='th_packages' and column_name in ('tradeid','packageid','packagetype')\G *************************** 1. row *************************** TABLE_CATALOG: def TABLE_SCHEMA: lt1_tradehouse_trade02 TABLE_NAME: th_packages NON_UNIQUE: 0 INDEX_SCHEMA: lt1_tradehouse_trade02 INDEX_NAME: PRIMARY SEQ_IN_INDEX: 1 COLUMN_NAME: packageid COLLATION: A CARDINALITY: 1501916946 SUB_PART: NULL PACKED: NULL NULLABLE: INDEX_TYPE: BTREE COMMENT: INDEX_COMMENT: *************************** 2. row *************************** TABLE_CATALOG: def TABLE_SCHEMA: lt1_tradehouse_trade02 TABLE_NAME: th_packages NON_UNIQUE: 1 INDEX_SCHEMA: lt1_tradehouse_trade02 INDEX_NAME: th_packages_tradeid SEQ_IN_INDEX: 1 COLUMN_NAME: tradeid COLLATION: A CARDINALITY: 2 SUB_PART: NULL PACKED: NULL NULLABLE: INDEX_TYPE: BTREE COMMENT: INDEX_COMMENT: Slave 261: mysql> select * from STATISTICS where TABLE_SCHEMA='lt1_tradehouse_trade02' and table_name='th_packages' and column_name in ('tradeid','packageid','packagetype')\G *************************** 1. row *************************** TABLE_CATALOG: def TABLE_SCHEMA: lt1_tradehouse_trade02 TABLE_NAME: th_packages NON_UNIQUE: 0 INDEX_SCHEMA: lt1_tradehouse_trade02 INDEX_NAME: PRIMARY SEQ_IN_INDEX: 1 COLUMN_NAME: packageid COLLATION: A CARDINALITY: 1249288359 SUB_PART: NULL PACKED: NULL NULLABLE: INDEX_TYPE: BTREE COMMENT: INDEX_COMMENT: *************************** 2. row *************************** TABLE_CATALOG: def TABLE_SCHEMA: lt1_tradehouse_trade02 TABLE_NAME: th_packages NON_UNIQUE: 1 INDEX_SCHEMA: lt1_tradehouse_trade02 INDEX_NAME: th_packages_tradeid SEQ_IN_INDEX: 1 COLUMN_NAME: tradeid COLLATION: A CARDINALITY: 1249288359 SUB_PART: NULL PACKED: NULL NULLABLE: INDEX_TYPE: BTREE COMMENT: INDEX_COMMENT: How to repeat: I am not sure how o repeat this behavior, but this is happening for some other server also which has this schema. After I do analyze table, it appears again after some time.