How to index a query with many joins

I have this query with [this schema](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)