Simple Query optimization using ORDER BY

Question

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'

• jiten: looks like expiry_date is the culprit, please suggest
• Vérace: Give us a SHOW CREATE TABLE asset\G. Run an EXPLAIN on the query. How many records in this table?
• jiten: please see the update to the main question: the table has more than 2M records and the query results in 260,000 records.

Answer 1

You should add one of these two indexes:

ALTER TABLE asset 
ADD INDEX product_id_status_expiry_date(product_id, status, expiry_date);

This will help with the filtering.

ALTER TABLE asset 
ADD INDEX expiry_date_serial_number(expiry_date, serial_number);

This will help with the filesort.

Only one of them can be used at a time, so add only the most advantageous.

• jiten: thanks, will surely look into the solution. Any other inputs without touching the index will be great as there are other queries that are using this table.
• jiten: this has worked thanks for the suggestion. From 10 secs query is now taking 1.6 secs, the requirement is that the query should take no longer than 50 milliseconds...is there anything else that can be done?
• jynus: @jiten You should create a new question providing the additional details of your requirements, it is probably not going to be trivial.