CODEWITHSUNDEEP
mysql
oka96
oka96

Reputation: 423

MYSQL Array Variable (No store prodecure, No temporarily table)

As mention in the title, I would like to know any solution for this by not using store prodecure, temporarily table etc.

Compare Query#1 and Query#3, Query#3 get worst performance result. Does it have any workaround to put in variable but without impact the performance result.

Schema (MySQL v5.7)

create table `order`(
    id BIGINT(20) not null auto_increment,
    transaction_no varchar(20) not null,
    primary key (`id`),
    unique key uk_transaction_no (`transaction_no`)
);

insert into `order` (`id`,`transaction_no`)
value (1,'10001'),(2,'10002'),(3,'10003'),(4,'10004');

Query #1

explain select * from `order` where transaction_no in ('10001','10004');
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE order range uk_transaction_no uk_transaction_no 22 2 100 Using where; Using index

Query #2

set @transactionNos = "10001,10004";

There are no results to be displayed.

Query #3

explain select * from `order` where find_in_set(transaction_no, @transactionNos);
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE order index uk_transaction_no 22 4 100 Using where; Using index

Upvotes: 0

Views: 53

Answers (1)

Rick James
Rick James

Reputation: 142296

Short Answer: See Sargeability

Long Answer:

MySQL makes no attempt to optimize an expression when an indexed column when it is hidden inside a function call such as FIND_IN_SET(), DATE(), etc. Your Query 3 will always be performed as a full table scan or a full index scan.

So the only way to optimize what you are doing is to construct

IN ('10001','10004')

That is often difficult to achieve when "binding" a list of values. IN(?) will not work in most APIs to MySQL.

Upvotes: 2

Related Questions