1

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'

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

1 Answer 1

0

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.

3
  • 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
    Aug 29, 2014 at 6:29
  • 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?
    – jiten
    Aug 29, 2014 at 7:16
  • @jiten You should create a new question providing the additional details of your requirements, it is probably not going to be trivial.
    – jynus
    Aug 29, 2014 at 10:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.