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'

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.