The following is a simple query that is taking more than 10 seconds to execute:
select * from inventory.asset ass where ass.product_id=83 and ass.expiry_date > now() and ass.status=1 order by ass.expiry_date ASC, ass.serial_number ASC limit 10 for update
create table asset (
id serial,
action_date timestamp not null default current_timestamp on update current_timestamp,
curramt int check (curramt >=0),
features text,
initamt int check (initamt >=0),
opfield1_value varchar(24),
product_id bigint unsigned not null,
serial_number varchar(255) not null,
status tinyint unsigned not null,
version int,
export_id bigint unsigned,
import_id bigint unsigned,
expiry_date date not null,
primary key (id),
unique (serial_number, product_id)
) ENGINE=INNODB DEFAULT CHARSET=utf8;
create index ix_asset_import_id on asset (import_id);
create index ix_asset_expiry_date on asset (expiry_date);
create index ix_asset_export_id on asset (export_id);
create index ix_asset_serial_number on asset (serial_number);
create index ix_asset_product_id on asset (product_id);
alter table asset
add constraint fk_asset_import
foreign key (import_id)
references shipment(id);
alter table asset
add constraint fk_asset_export
foreign key (export_id)
references shipment(id);
EXPLAIN results:
id, select_type, table, type, possible_keys, key, key_len, ref, rows, Extra
'1', 'SIMPLE', 'ass', 'ref', 'fk_asset_status,ix_asset_expiry_date,ix_asset_product_id', 'fk_asset_status', '1', 'const', '2001344', 'Using where; Using filesort'
EXPLAIN EXTENDED results:
id, select_type, table, type, possible_keys, key, key_len, ref, rows, filtered, Extra
'1', 'SIMPLE', 'ass', 'ref', 'fk_asset_status,ix_asset_expiry_date,ix_asset_product_id', 'fk_asset_status', '1', 'const', '2001344', '100.00', 'Using where; Using filesort'