mysqlのdelete文でindexが使われない

Question

質問

下記のようなテーブルがありphpよりdeleteした所、かなりの時間を要しました。
explainしたみた結果、indexが使われていないことがわかりました。
なぜかindexが利用されません。

• php code

public function products_delete($code, $products_id)
{
  $this->db->where(['code' => $code, 'products_id' => $products_id])->delete('master');
}

• テーブル構造

CREATE TABLE `master` (
  `code` varchar(50) NOT NULL,
  `products_id` int(10) unsigned NOT NULL,
  `code_id` int(10) unsigned NOT NULL,
  UNIQUE KEY `code_products_id` (`code`,`products_id`),
  KEY `code_` (`code`),
  KEY `code_id` (`code_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

• 発行したSQL

explain delete from master where code = 1009000100 and products_id = 0

• 結果

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	DELETE	master	NULL	ALL	NULL	NULL	NULL	NULL	5546430	100.00	Using where

試したこと1

• '1009000100' とシングルクォートで囲ったらindexが使われた

explain delete from master where code = '1009000100' and products_id = 0;

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	DELETE	master	NULL	range	code_products_id,code_	code_products_id	156	const,const	1	100.00	Using where

試したこと2

• select文ではindexが利用される

explain select * from master where code = 1009000100 and products_id = 0;

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	master	NULL	index	code_products_id,code_	code_id	4	NULL	5546430	1.00	Using where; Using index

推測

• select文だと条件のcodeが文字列に変換されて実行されるからindexが利用される？
• delete文だと型変換がされずそのまま実行されてしまう？

補足情報（FW/ツールのバージョンなど）

• mysql 5.7
• php 7.1
• codeigniter 1.7

よろしくおねがいします。

Comments

[m.ts10806]

PHPコードに問題がないとも言い切れません。
PHP側のコードもご提示ください（あとタグにも追加し、バージョンも記載してください）

[rotaliano]

ご意見ありがとうございます。
追記しました。

Answer 1

code varchar(50) NOT NULL,

products_id int(10) unsigned NOT NULL,

だから、

delete from master where code = 1009000100 and products_id = 0

は、code が文字型で 1009000100 は数値ですから、master テーブル全件の code を数値型に変換してから1009000100 と比較します。だから、インデックスは使われません。
データ型の扱いにはシビアになれないと、この仕事への適性を疑われます。

Comments

[rotaliano]

ご回答ありがとうございます。
ちなみにselect文はオプティマイザが良しなに型変換してくれるのでしょうか？

[Orlofsky]

オプティマイザは同じデータ型にしないと比較できないからです。決して「良しな」ではありません。
SQLの文法として、最初から
delete from master where code = '1009000100' and products_id = 0
と文字列はシングルクォーティング single quoting (シングルクォートで囲む)してください。

Answer 2

誤ったSQL文の挙動が場合によって異なるのに、理由などありません。
仕様です、と納得するしかありません。
正しいSQL文を書けばいいだけのことです。

Comments

[rotaliano]

ご回答ありがとうございます。
query builderを利用してました。
型を意識して渡さないとダメなんですね。
※php code 追記しました。