How to index a query with many joins



  • I have this query with https://github.com/snurr-group/mofdb/blob/master/db/schema.rb that takes ~8s on my laptop and longer on the vm running the service.

    SELECT DISTINCT `mofs`.*
    FROM `mofs`
             INNER JOIN `isotherms` ON `isotherms`.`mof_id` = `mofs`.`id`
             INNER JOIN `isodata` ON `isodata`.`isotherm_id` = `isotherms`.`id`
    WHERE `mofs`.`hidden` = FALSE
      AND (isodata.gas_id in (24))
      AND (volumeA3 is not NULL and atomicMass is not NULL)
    ORDER BY `mofs`.`id` ASC
    LIMIT 100;
    

    Essentially I join mofs->isotherms->isodata (all simple one-many relationships) and select only the mofs with isodata.gas_id present in a list supplied by the user (24) in this case but there could be 1-6 numbes here.

    There are a few constant where conditions mofs.hidden = false mofs.volumeA3 is not NULL and mofs.atomicMass is not NULL these are the same for every query.

    I've tried adding an index :mofs, [:volumeA3, :atomicMass, :hidden], where: 'volumeA3 IS NOT NULL and atomicMass IS NOT NULL and hidden is FALSE' to capture the static part of the query. This doesn't make it notably faster.

    Query explainer:

    mysql> EXPLAIN SELECT DISTINCT `mofs`.* FROM `mofs`          INNER JOIN `isotherms` ON `isotherms`.`mof_id` = `mofs`.`id`          INNER JOIN `isodata` ON `isodata`.`isotherm_id` = `isotherms`.`id` WHERE `mofs`.`hidden` = FALSE   AND (isodata.gas_id in (24))   AND (volumeA3 is not NULL and atomicMass is not NULL) ORDER BY `mofs`.`id` ASC LIMIT 100;
    +----+-------------+-----------+------------+------+-------------------------------------------------------------------------------------------------------------------------------+------------------------------+---------+--------------------------------+-------+----------+------------------------------+
    | id | select_type | table     | partitions | type | possible_keys                                                                                                                 | key                          | key_len | ref                            | rows  | filtered | Extra                        |
    +----+-------------+-----------+------------+------+-------------------------------------------------------------------------------------------------------------------------------+------------------------------+---------+--------------------------------+-------+----------+------------------------------+
    |  1 | SIMPLE      | mofs      | NULL       | ref  | PRIMARY,index_mofs_on_hidden,index_mofs_on_volumeA3,index_mofs_on_atomicMass,index_mofs_on_volumeA3_and_atomicMass_and_hidden | index_mofs_on_hidden         | 1       | const                          | 60373 |    25.00 | Using where; Using temporary |
    |  1 | SIMPLE      | isotherms | NULL       | ref  | PRIMARY,index_isotherms_on_mof_id                                                                                             | index_isotherms_on_mof_id    | 9       | mofdb2_prod_dump2.mofs.id      |     5 |   100.00 | Using index; Distinct        |
    |  1 | SIMPLE      | isodata   | NULL       | ref  | index_isodata_on_isotherm_id,index_isodata_on_gas_id                                                                          | index_isodata_on_isotherm_id | 9       | mofdb2_prod_dump2.isotherms.id |     3 |    41.45 | Using where; Distinct        |
    +----+-------------+-----------+------------+------+-------------------------------------------------------------------------------------------------------------------------------+------------------------------+---------+--------------------------------+-------+----------+------------------------------+
    

    How can I speed up this query? Is there a way to add an index that captures the mof->isotherm->isodata.gas_id relationship ? Eg. In postgres sql I could create a materialized view like this:

    mof_id|gas_id
    1|350
    1|33
    2|5
    2|33
    ...
    

    and then query that view. Can I achieve something similar with an index?

    Schema

    mysql> show create table mofs;
    +-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    | Table | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     |
    +-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    | mofs  | CREATE TABLE `mofs` (
      `id` bigint NOT NULL AUTO_INCREMENT,
      `hashkey` varchar(255) DEFAULT NULL,
      `name` varchar(255) DEFAULT NULL,
      `database_id` bigint DEFAULT NULL,
      `cif` mediumtext,
      `void_fraction` float DEFAULT NULL,
      `surface_area_m2g` float DEFAULT NULL,
      `surface_area_m2cm3` float DEFAULT NULL,
      `pld` float DEFAULT NULL,
      `lcd` float DEFAULT NULL,
      `pxrd` text,
      `pore_size_distribution` text,
      `created_at` datetime NOT NULL,
      `updated_at` datetime NOT NULL,
      `pregen_json` json DEFAULT NULL,
      `mofid` text,
      `mofkey` text,
      `hidden` tinyint(1) NOT NULL DEFAULT '0',
      `atomicMass` float DEFAULT NULL,
      `volumeA3` float DEFAULT NULL,
      `batch_id` bigint DEFAULT NULL,
      PRIMARY KEY (`id`),
      KEY `fk_rails_42b2867304` (`database_id`),
      KEY `index_mofs_on_hashkey` (`hashkey`),
      KEY `index_mofs_on_name` (`name`),
      KEY `index_mofs_on_hidden` (`hidden`),
      KEY `index_mofs_on_pld` (`pld`),
      KEY `index_mofs_on_lcd` (`lcd`),
      KEY `index_mofs_on_void_fraction` (`void_fraction`),
      KEY `index_mofs_on_surface_area_m2g` (`surface_area_m2g`),
      KEY `index_mofs_on_surface_area_m2cm3` (`surface_area_m2cm3`),
      KEY `mofid_exact_match_idx` (`mofid`(768)),
      KEY `mofkey_exact_match_idx` (`mofkey`(768)),
      KEY `index_mofs_on_volumeA3` (`volumeA3`),
      KEY `index_mofs_on_atomicMass` (`atomicMass`),
      KEY `index_mofs_on_batch_id` (`batch_id`),
      KEY `index_mofs_on_volumeA3_and_atomicMass_and_hidden` (`volumeA3`,`atomicMass`,`hidden`),
      CONSTRAINT `fk_rails_42b2867304` FOREIGN KEY (`database_id`) REFERENCES `databases` (`id`),
      CONSTRAINT `fk_rails_c2906db3ef` FOREIGN KEY (`batch_id`) REFERENCES `batches` (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=167396 DEFAULT CHARSET=utf8 |
    +-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    1 row in set (0.00 sec)
    

    mysql> show create table isotherms;
    +-----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    | Table | Create Table |
    +-----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    | isotherms | CREATE TABLE isotherms (
    id bigint NOT NULL AUTO_INCREMENT,
    doi varchar(255) DEFAULT NULL,
    digitizer varchar(255) DEFAULT NULL,
    temp float DEFAULT NULL,
    simin text,
    adsorbate_forcefield_id bigint DEFAULT NULL,
    molecule_forcefield_id bigint DEFAULT NULL,
    mof_id bigint DEFAULT NULL,
    adsorption_units_id bigint DEFAULT NULL,
    pressure_units_id bigint DEFAULT NULL,
    composition_type_id bigint DEFAULT NULL,
    created_at datetime NOT NULL,
    updated_at datetime NOT NULL,
    batch_id bigint DEFAULT NULL,
    PRIMARY KEY (id),
    KEY index_isotherms_on_mof_id (mof_id),
    KEY index_isotherms_on_adsorption_units_id (adsorption_units_id),
    KEY index_isotherms_on_pressure_units_id (pressure_units_id),
    KEY index_isotherms_on_composition_type_id (composition_type_id),
    KEY fk_rails_8886e0d88b (adsorbate_forcefield_id),
    KEY fk_rails_180e64ceb3 (molecule_forcefield_id),
    KEY index_isotherms_on_doi (doi),
    KEY index_isotherms_on_batch_id (batch_id),
    CONSTRAINT fk_rails_10527b19a8 FOREIGN KEY (composition_type_id) REFERENCES classifications (id),
    CONSTRAINT fk_rails_180e64ceb3 FOREIGN KEY (molecule_forcefield_id) REFERENCES forcefields (id),
    CONSTRAINT fk_rails_1b8cd34a98 FOREIGN KEY (pressure_units_id) REFERENCES classifications (id),
    CONSTRAINT fk_rails_7931af24f5 FOREIGN KEY (adsorption_units_id) REFERENCES classifications (id),
    CONSTRAINT fk_rails_8886e0d88b FOREIGN KEY (adsorbate_forcefield_id) REFERENCES forcefields (id),
    CONSTRAINT fk_rails_94b5964f6a FOREIGN KEY (mof_id) REFERENCES mofs (id),
    CONSTRAINT fk_rails_ea429d3060 FOREIGN KEY (batch_id) REFERENCES batches (id)
    ) ENGINE=InnoDB AUTO_INCREMENT=6368886 DEFAULT CHARSET=utf8 |
    +-----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    1 row in set (0.00 sec)

    mysql> show create table isodata;
    +---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    | Table | Create Table |
    +---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    | isodata | CREATE TABLE isodata (
    id bigint NOT NULL AUTO_INCREMENT,
    isotherm_id bigint DEFAULT NULL,
    gas_id bigint DEFAULT NULL,
    pressure float DEFAULT NULL,
    loading float DEFAULT NULL,
    bulk_composition float DEFAULT NULL,
    PRIMARY KEY (id),
    KEY index_isodata_on_isotherm_id (isotherm_id),
    KEY index_isodata_on_gas_id (gas_id),
    CONSTRAINT fk_rails_279fe04b2e FOREIGN KEY (isotherm_id) REFERENCES isotherms (id),
    CONSTRAINT fk_rails_b1baf3536c FOREIGN KEY (gas_id) REFERENCES gases (id)
    ) ENGINE=InnoDB AUTO_INCREMENT=17313801 DEFAULT CHARSET=utf8 |
    +---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    1 row in set (0.00 sec)



  • If isotherms_mofs_join is a many-to-many table, then it probably needs better indexes; see http://mysql.rjweb.org/doc.php/index_cookbook_mysql#many_to_many_mapping_table




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2