We are working on a view (multiple joins in this view). And I have these two queries:
- Query 1:
select * from v_investment_planning_ticket v where v.ticket_id in (select ti.ticket_id from ticket_item ti where ti.status = 'SENT') order by v.period_created desc; - Query 2:
select * from v_investment_planning_ticket v where v.ticket_id in (select ti.ticket_id from ticket_item ti where ti.status = 'SENT') and (v.ticket_status in ('NEW', 'NOT_DEPOSIT_IN', 'CASH_NOT_ENOUGH', 'CASH_ENOUGH', 'EXPIRED_DEPOSIT', 'PENDING', 'PROCESSING', 'EXPIRED', 'REJECTED', 'PARTIALLY_SUCCESSFUL', 'SUCCESSFUL', 'FAILED')) order by v.period_created desc;
I can say that the meaning of the second query is the same. But with the first one MySQL takes ~40 seconds each time to query it. And with the second one it only takes ~0.7 seconds. And the difference is I added one more condition in the 2nd query with ALL the ticket status values I have.
Please tell me why, when I do that, it's so different.
Additional details
Query 1 EXPLAIN:
[ { "id": 1, "select_type": "SIMPLE", "table": "<subquery2>", "partitions": null, "type": "ALL", "possible_keys": null, "key": null, "key_len": null, "ref": null, "rows": null, "filtered": 100, "Extra": "Using temporary; Using filesort" }, { "id": 1, "select_type": "SIMPLE", "table": "tk", "partitions": null, "type": "eq_ref", "possible_keys": "PRIMARY,ticket_investment_planning_id_fk,ticket_target_term_period_id_fk", "key": "PRIMARY", "key_len": "8", "ref": "<subquery2>.ticket_id", "rows": 1, "filtered": 100, "Extra": null }, { "id": 1, "select_type": "SIMPLE", "table": "p", "partitions": null, "type": "ALL", "possible_keys": "PRIMARY,investment_planning_target_id_fk", "key": null, "key_len": null, "ref": null, "rows": 12133, "filtered": 0.01, "Extra": "Using where; Using join buffer (Block Nested Loop)" }, { "id": 1, "select_type": "SIMPLE", "table": "ttp", "partitions": null, "type": "ALL", "possible_keys": "PRIMARY", "key": null, "key_len": null, "ref": null, "rows": 5, "filtered": 20, "Extra": "Using where; Using join buffer (Block Nested Loop)" }, { "id": 1, "select_type": "SIMPLE", "table": "t", "partitions": null, "type": "eq_ref", "possible_keys": "PRIMARY", "key": "PRIMARY", "key_len": "8", "ref": "dgo_db.p.target_id", "rows": 1, "filtered": 100, "Extra": "Using index" }, { "id": 2, "select_type": "MATERIALIZED", "table": "ti", "partitions": null, "type": "ALL", "possible_keys": "ticket_item_ticket_id_fk", "key": null, "key_len": null, "ref": null, "rows": 42797, "filtered": 14.29, "Extra": "Using where" } ]Query 2 EXPLAIN:
[ { "id": 1, "select_type": "SIMPLE", "table": "ti", "partitions": null, "type": "ALL", "possible_keys": "ticket_item_ticket_id_fk", "key": null, "key_len": null, "ref": null, "rows": 42797, "filtered": 14.29, "Extra": "Using where; Using temporary; Using filesort; Start temporary" }, { "id": 1, "select_type": "SIMPLE", "table": "tk", "partitions": null, "type": "eq_ref", "possible_keys": "PRIMARY,ticket_investment_planning_id_fk,ticket_target_term_period_id_fk", "key": "PRIMARY", "key_len": "8", "ref": "dgo_db.ti.ticket_id", "rows": 1, "filtered": 50, "Extra": "Using where; End temporary" }, { "id": 1, "select_type": "SIMPLE", "table": "ttp", "partitions": null, "type": "ALL", "possible_keys": "PRIMARY", "key": null, "key_len": null, "ref": null, "rows": 5, "filtered": 20, "Extra": "Using where; Using join buffer (Block Nested Loop)" }, { "id": 1, "select_type": "SIMPLE", "table": "p", "partitions": null, "type": "eq_ref", "possible_keys": "PRIMARY,investment_planning_target_id_fk", "key": "PRIMARY", "key_len": "8", "ref": "dgo_db.tk.investment_plan_id", "rows": 1, "filtered": 100, "Extra": "Using where" }, { "id": 1, "select_type": "SIMPLE", "table": "t", "partitions": null, "type": "eq_ref", "possible_keys": "PRIMARY", "key": "PRIMARY", "key_len": "8", "ref": "dgo_db.p.target_id", "rows": 1, "filtered": 100, "Extra": "Using index" } ]View
v_investment_planning_ticket:select `p`.`id` AS `plan_id`, `p`.`customer_id` AS `customer_id`, `p`.`customer_name` AS `customer_name`, `p`.`custody_code` AS `custody_code`, `p`.`account_no` AS `account_no`, `p`.`tag_text` AS `tag_text`, `p`.`target_id` AS `target_id`, `p`.`target_name` AS `target_name`, `p`.`business_line_id` AS `business_line_id`, `p`.`business_line_name` AS `business_line_name`, `p`.`business_line_code` AS `business_line_code`, `p`.`product_key` AS `product_key`, `p`.`target_portfolio` AS `target_portfolio`, `p`.`target_attribute` AS `target_attribute`, `p`.`careby_id` AS `careby_id`, `p`.`careby_name` AS `careby_name`, `p`.`department_id` AS `department_id`, `p`.`department_name` AS `department_name`, `p`.`execution_method` AS `execution_method`, `p`.`creator` AS `creator`, `tk`.`id` AS `ticket_id`, `tk`.`status` AS `ticket_status`, `tk`.`total_amount` AS `total_amount`, `tk`.`total_quantity` AS `total_quantity`, `tk`.`total_located_amount` AS `total_located_amount`, `tk`.`total_located_quantity` AS `total_located_quantity`, `tk`.`careby_id` AS `ticket_careby_id`, `ttp`.`id` AS `period_id`, `ttp`.`name` AS `period_name`, `ttp`.`year` AS `period_year`, `ttp`.`start_time` AS `period_start_time`, `ttp`.`end_time` AS `period_end_time`, `ttp`.`created` AS `period_created` from (((`investment_planning` `p` join `target` `t` on ((`p`.`target_id` = `t`.`id`))) join `ticket` `tk` on ((`p`.`id` = `tk`.`investment_plan_id`))) join `target_term_period` `ttp` on ((`ttp`.`id` = `tk`.`period_id`)));Table
ticket_item:create table ticket_item ( id bigint unsigned auto_increment primary key, ticket_id bigint unsigned not null, status enum ('PENDING', 'PROCESSING', 'SENT', 'FILLED', 'PARTIALLY_FILLED', 'FAILED', 'REJECTED') not null, error_description varchar(255) null, trading_date date null, sec_type varchar(50) null, trans_ref varchar(50) null, code_id varchar(50) null, symbol varchar(50) null, product_id varchar(100) null, product_code varchar(50) null, quantity decimal(12, 3) null, amount decimal(15, 3) null, located_quantity decimal(12, 3) null, located_amount decimal(15, 3) null, price decimal(15, 3) null, rate decimal(6, 3) null, created datetime not null, modified timestamp default CURRENT_TIMESTAMP null on update CURRENT_TIMESTAMP, constraint ticket_item_ticket_id_fk foreign key (ticket_id) references ticket (id) );Table
ticket:create table ticket ( id bigint unsigned auto_increment primary key, code varchar(100) not null, investment_plan_id bigint unsigned not null, status enum ('NEW', 'NOT_DEPOSIT_IN', 'CASH_NOT_ENOUGH', 'CASH_ENOUGH', 'EXPIRED_DEPOSIT', 'PENDING', 'PROCESSING', 'EXPIRED', 'REJECTED', 'PARTIALLY_SUCCESSFUL', 'SUCCESSFUL', 'FAILED') not null, total_quantity decimal(12, 3) null, total_amount decimal(15, 3) null, total_located_quantity decimal(12, 3) null, total_located_amount decimal(15, 3) null, creator varchar(100) null, careby_id varchar(100) null, period_id bigint unsigned null, expired_time datetime null, careby_name varchar(255) null, created datetime not null, modified timestamp default CURRENT_TIMESTAMP null on update CURRENT_TIMESTAMP, constraint ticket_investment_planning_id_fk foreign key (investment_plan_id) references investment_planning (id), constraint ticket_target_term_period_id_fk foreign key (period_id) references target_term_period (id)Table
target:create table target ( id bigint unsigned auto_increment primary key, business_line_id bigint unsigned null, code varchar(100) null, name varchar(255) null, product_key varchar(100) null, type enum ('PORTFOLIO_REQUIRED', 'PORTFOLIO_EMPTY', 'PORTFOLIO_OPTIONAL') null, description varchar(500) null, active bit default b'0' null, creator varchar(255) null, created datetime null, modified timestamp default CURRENT_TIMESTAMP null on update CURRENT_TIMESTAMP, constraint fk_target_businessLine foreign key (business_line_id) references business_line (id) on update cascade );Table
investment_planning:create table investment_planning ( id bigint unsigned auto_increment primary key, customer_id varchar(100) null, customer_name varchar(255) null, careby_id varchar(100) null, careby_name varchar(100) null, department_id varchar(100) null, department_name varchar(255) null, custody_code varchar(500) null, account_no varchar(50) null, tag_text varchar(500) null, business_line_id bigint unsigned null, business_line_code varchar(45) null, business_line_name varchar(255) null, product_key varchar(45) null, target_id bigint unsigned null, target_name varchar(255) null, target_attribute json null, target_withdraw json null, target_portfolio json null, auto_execution bit default b'0' null, execution_method enum ('AUTO', 'SEMI_AUTO', 'MANUAL') null, status enum ('PENDING', 'WAITING', 'ACTIVE', 'DEACTIVATE', 'EXPIRED', 'CANCEL') null, creator varchar(100) null, editor varchar(255) null, created datetime null, edited datetime null, modified timestamp default CURRENT_TIMESTAMP null on update CURRENT_TIMESTAMP, constraint investment_planning_business_line_id_fk foreign key (business_line_id) references business_line (id), constraint investment_planning_target_id_fk foreign key (target_id) references target (id) );Table
target_term_period:create table target_term_period ( id bigint unsigned auto_increment primary key, target_id bigint unsigned not null, start_time datetime not null, end_time datetime not null, name varchar(50) not null, year int not null, created datetime null, modified timestamp default CURRENT_TIMESTAMP null on update CURRENT_TIMESTAMP, constraint target_term_period_target_id_start_time_end_time_uindex unique (target_id, start_time, end_time), constraint target_term_period_target_id_fk foreign key (target_id) references target (id) );
