How to index a query with many joins

[Burma]

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)

[elysha]

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