[MDEV-10381] USE INDEX HINT Created: 2016-07-16  Updated: 2016-08-08  Resolved: 2016-08-08

Status: Closed
Project: MariaDB Server
Component/s: Documentation, Storage Engine - InnoDB
Affects Version/s: 10.1.13
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: sungwon.han Assignee: Ian Gilfillan
Resolution: Fixed Votes: 0
Labels: None


 Description   

There is an abnormal situation when using the hint query. I'm using the hint(use index) with SELECT query. neverthless, the index is not exist, it is normally executed. (I delete the index before using hint). Why does it happen?

EXPLAIN
 
SELECT * FROM TB_LI_GLAP_KR_DOM_SHIP T /*! use index(TB_LI_GLAP_KR_DOM_SHIP_imsi) */
 
WHERE T.CORP_TYPE = 'LGEKR'
 
AND T.SOURCE_SYSTEM_CODE = 'GTMS_KR'
 
AND T.ATTRIBUTE40 in ('123','124','125','126') -- '103 ~ 118 
 
 
"id"	"select_type"	"table"	"type"	"possible_keys"	"key"	"key_len"	"ref"	"rows"	"Extra"
 
"1"	"SIMPLE"	"T"	"range"	"TB_LI_GLAP_KR_DOM_SHIP_imsi2"	"TB_LI_GLAP_KR_DOM_SHIP_imsi2"	"502"	\N	"1642176"	"Using index condition"
 
 
 
 
 
SHOW INDEX IN TB_LI_GLAP_KR_DOM_SHIP
 
 
 
"Table"	"Non_unique"	"Key_name"	"Seq_in_index"	"Column_name"	"Collation"	"Cardinality"	"Sub_part"	"Packed"	"Null"	"Index_type"	"Comment"	"Index_comment"
 
"tb_li_glap_kr_dom_ship"	"0"	"PRIMARY"	"1"	"interface_id"	"A"	"29382303"	\N	\N	""	"BTREE"	""	""
 
"tb_li_glap_kr_dom_ship"	"1"	"TB_LI_GLAP_KR_DOM_SHIP_n02"	"1"	"CORP_TYPE"	"A"	"454"	\N	\N	""	"BTREE"	""	""
 
"tb_li_glap_kr_dom_ship"	"1"	"TB_LI_GLAP_KR_DOM_SHIP_n02"	"2"	"dc_cd"	"A"	"6810"	\N	\N	""	"BTREE"	""	""
 
"tb_li_glap_kr_dom_ship"	"1"	"TB_LI_GLAP_KR_DOM_SHIP_n02"	"3"	"trans_corp_shipment_id"	"A"	"14691151"	\N	\N	""	"BTREE"	""	""
 
"tb_li_glap_kr_dom_ship"	"1"	"TB_LI_GLAP_KR_DOM_SHIP_n03"	"1"	"CORP_TYPE"	"A"	"464"	\N	\N	""	"BTREE"	""	""
 
"tb_li_glap_kr_dom_ship"	"1"	"TB_LI_GLAP_KR_DOM_SHIP_n03"	"2"	"source_system_code"	"A"	"2328"	\N	\N	""	"BTREE"	""	""
 
"tb_li_glap_kr_dom_ship"	"1"	"TB_LI_GLAP_KR_DOM_SHIP_n03"	"3"	"pg_ymd"	"A"	"27876"	\N	\N	""	"BTREE"	""	""
 
"tb_li_glap_kr_dom_ship"	"1"	"TB_LI_GLAP_KR_DOM_SHIP_n04"	"1"	"CORP_TYPE"	"A"	"802"	\N	\N	""	"BTREE"	""	""
 
"tb_li_glap_kr_dom_ship"	"1"	"TB_LI_GLAP_KR_DOM_SHIP_n04"	"2"	"attribute23"	"A"	"802"	\N	\N	"YES"	"BTREE"	""	""
 
"tb_li_glap_kr_dom_ship"	"1"	"TB_LI_GLAP_KR_DOM_SHIP_n04"	"3"	"attribute24"	"A"	"802"	\N	\N	"YES"	"BTREE"	""	""
 
"tb_li_glap_kr_dom_ship"	"1"	"tb_li_glap_kr_dom_ship_n05"	"1"	"back_transfer_flag"	"A"	"2"	\N	\N	"YES"	"BTREE"	""	""
 
"tb_li_glap_kr_dom_ship"	"1"	"TB_LI_GLAP_KR_DOM_SHIP_N01"	"1"	"source_system_code"	"A"	"1778"	\N	\N	""	"BTREE"	""	""
 
"tb_li_glap_kr_dom_ship"	"1"	"TB_LI_GLAP_KR_DOM_SHIP_N01"	"2"	"transfer_flag"	"A"	"5340"	\N	\N	""	"BTREE"	""	""
 
"tb_li_glap_kr_dom_ship"	"1"	"TB_LI_GLAP_KR_DOM_SHIP_N01"	"3"	"CORP_TYPE"	"A"	"5932"	\N	\N	""	"BTREE"	""	""
 
"tb_li_glap_kr_dom_ship"	"1"	"TB_LI_GLAP_KR_DOM_SHIP_N01"	"4"	"dc_cd"	"A"	"7713"	\N	\N	""	"BTREE"	""	""
 
"tb_li_glap_kr_dom_ship"	"1"	"TB_LI_GLAP_KR_DOM_SHIP_imsi2"	"1"	"CORP_TYPE"	"A"	"638"	\N	\N	""	"BTREE"	""	""
 
"tb_li_glap_kr_dom_ship"	"1"	"TB_LI_GLAP_KR_DOM_SHIP_imsi2"	"2"	"source_system_code"	"A"	"3198"	\N	\N	""	"BTREE"	""	""
 
"tb_li_glap_kr_dom_ship"	"1"	"TB_LI_GLAP_KR_DOM_SHIP_imsi2"	"3"	"attribute40"	"A"	"102377"	\N	\N	"YES"	"BTREE"	""	""
 
"tb_li_glap_kr_dom_ship"	"1"	"TB_LI_GLAP_KR_DOM_SHIP_imsi2"	"4"	"transfer_flag"	"A"	"104192"	\N	\N	""	"BTREE"	""	""
 
 
 
 
 
SHOW TABLE STATUS LIKE 'TB_LI_GLAP_KR_DOM_SHIP'
 
"Name"	"Engine"	"Version"	"Row_format"	"Rows"	"Avg_row_length"	"Data_length"	"Max_data_length"	"Index_length"	"Data_free"	"Auto_increment"	"Create_time"	"Update_time"	"Check_time"	"Collation"	"Checksum"	"Create_options"	"Comment"
 
"tb_li_glap_kr_dom_ship"	"InnoDB"	"10"	"Compact"	"29382303"	"1282"	"37694210048"	"0"	"10643718144"	"481296384"	\N	"2016-07-14 23:06:34"	\N	\N	"utf8_bin"	\N	""	""



 Comments   
Comment by Elena Stepanova [ 2016-07-16 ]

gks3117,
As MySQL manual says, index hints can contain prefixes of index names, not necessarily full names.
http://dev.mysql.com/doc/refman/5.6/en/index-hints.html

An index_name value need not be a full index name. It can be an unambiguous prefix of an index name. If a prefix is ambiguous, an error occurs.

You still have an index with this prefix, and it's unambiguous, so it fits.

greenman,
I couldn't find this in MariaDB KB. It's a non-obvious detail, so better to have it documented, e.g. here:
https://mariadb.com/kb/en/mariadb/how-to-force-query-plans/

Also, sadly this page does not show up anywhere close to the top of the search for 'USE INDEX' – I don't know if there is anything that can be done about it, but if there is, it would be good to fix.

Comment by Ian Gilfillan [ 2016-08-08 ]

Added to the documentation, and also created USE/IGNORE/FORCE INDEX pages that link to the extended page.

Generated at Thu Feb 08 07:41:48 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.