optimization with subquery not working as expected

Question

I have a MySQL InnoDB database. One table called affymetrixProbeset contains more than 300 million rows.

Querying this table with INNER JOIN to other tables, with an ORDER BY and offset/limit takes almost 4 minutes. I saw that creating a subquery could be more optimized. I tried to move from my initial query with INNER JOIN to an approach using a subquery

The query is:

SELECT affymetrixProbeset.* 
FROM affymetrixProbeset 
WHERE affymetrixProbeset.bgeeAffymetrixChipId IN(
    SELECT affymetrixChip.bgeeAffymetrixChipId 
    FROM affymetrixChip INNER JOIN cond ON affymetrixChip.conditionId = cond.conditionId
    WHERE cond.speciesId = 9606)
order by affymetrixProbeset.affymetrixProbesetId, affymetrixProbeset.bgeeGeneId
limit 10;

The columns used in the ORDER BY correspond to the primary key of the table affymetrixProbeset.
The explain related to this query is

+----+-------------+--------------------+------------+--------+------------------------------+-------------+---------+--------------------------------------------------+-------+----------+----------------------------------------------+
| id | select_type | table              | partitions | type   | possible_keys                | key         | key_len | ref                                              | rows  | filtered | Extra                                        |
+----+-------------+--------------------+------------+--------+------------------------------+-------------+---------+--------------------------------------------------+-------+----------+----------------------------------------------+
|  1 | SIMPLE      | affymetrixChip     | NULL       | index  | PRIMARY,conditionId          | conditionId | 3       | NULL                                             | 12990 |   100.00 | Using index; Using temporary; Using filesort |
|  1 | SIMPLE      | cond               | NULL       | eq_ref | PRIMARY,speciesId            | PRIMARY     | 3       | bgee_v15_dev.affymetrixChip.conditionId          |     1 |    10.99 | Using where                                  |
|  1 | SIMPLE      | affymetrixProbeset | NULL       | ref    | PRIMARY,bgeeAffymetrixChipId | PRIMARY     | 3       | bgee_v15_dev.affymetrixChip.bgeeAffymetrixChipId |   175 |   100.00 | NULL                                         |
+----+-------------+--------------------+------------+--------+------------------------------+-------------+---------+--------------------------------------------------+-------+----------+----------------------------------------------+

The explain is exactly the same as my initial query. The query takes the same time (~4 minutes)

It looks like the ORDER BY is not done on the affymetrixProbeset table but directly on the affymetrixChip table that is part of the subquery. I was expecting MySQL to run the subquery and then order using the affymetrixProbeset table. Could you explain to me why it is not done as I expected?

I checked also the performance of separating this query in two.

The first one is:

SELECT affymetrixChip.bgeeAffymetrixChipId 
FROM affymetrixChip INNER JOIN cond ON affymetrixChip.conditionId = cond.conditionId
WHERE cond.speciesId = 9606;

I then passed the bgeeAffymetrixChipIds to the query

SELECT affymetrixProbeset.* 
FROM affymetrixProbeset 
WHERE affymetrixProbeset.bgeeAffymetrixChipId IN(.....)
order by affymetrixProbeset.affymetrixProbesetId, affymetrixProbeset.bgeeGeneId
limit 10;

I wrote ..... in the query as I had more than 5000 IDs.
The query run almost instantaneous as it order using the primary key.

Could you please explain why the subquery did not perform as I expected? Is there an other option to optimize the query than creating 2 queries?

UPDATE:

After adding some indexes proposed by @Rick, the explain now looks like :

+----+-------------+--------------------+------------+--------+-----------------------------------------------------+-------------+---------+------------------------------------------------+-------+----------+----------------------------------------------+
| id | select_type | table              | partitions | type   | possible_keys                                       | key         | key_len | ref                                            | rows  | filtered | Extra                                        |
+----+-------------+--------------------+------------+--------+-----------------------------------------------------+-------------+---------+------------------------------------------------+-------+----------+----------------------------------------------+
|  1 | SIMPLE      | affymetrixChip     | NULL       | index  | PRIMARY,conditionId                                 | conditionId | 6       | NULL                                           | 12561 |   100.00 | Using index; Using temporary; Using filesort |
|  1 | SIMPLE      | cond               | NULL       | eq_ref | PRIMARY,speciesId                                   | PRIMARY     | 3       | bgee_v15_0.affymetrixChip.conditionId          |     1 |    10.94 | Using where                                  |
|  1 | SIMPLE      | affymetrixProbeset | NULL       | ref    | PRIMARY,bgeeAffymetrixChipId_2,bgeeAffymetrixChipId | PRIMARY     | 3       | bgee_v15_0.affymetrixChip.bgeeAffymetrixChipId |   174 |   100.00 | NULL                                         |
+----+-------------+--------------------+------------+--------+-----------------------------------------------------+-------------+---------+------------------------------------------------+-------+----------+----------------------------------------------+

The query still takes 3 minutes to run.
The SHOW CREATE TABLE of the PK/FK/indexes of these tables are :

# table cond
  PRIMARY KEY (`conditionId`),
  UNIQUE KEY `speciesId` (`speciesId`,`conditionId`),
  UNIQUE KEY `anatEntityId` (`anatEntityId`,`cellTypeId`,`stageId`,`speciesId`,`sex`,`sexInferred`,`strain`),
  KEY `exprMappedConditionId` (`exprMappedConditionId`),
  KEY `cellTypeId` (`cellTypeId`),
  KEY `stageId` (`stageId`),
  CONSTRAINT `cond_ibfk_1` FOREIGN KEY (`exprMappedConditionId`) REFERENCES `cond` (`conditionId`) ON DELETE CASCADE,
  CONSTRAINT `cond_ibfk_2` FOREIGN KEY (`anatEntityId`) REFERENCES `anatEntity` (`anatEntityId`) ON DELETE CASCADE,
  CONSTRAINT `cond_ibfk_3` FOREIGN KEY (`cellTypeId`) REFERENCES `anatEntity` (`anatEntityId`) ON DELETE CASCADE,
  CONSTRAINT `cond_ibfk_4` FOREIGN KEY (`stageId`) REFERENCES `stage` (`stageId`) ON DELETE CASCADE,
  CONSTRAINT `cond_ibfk_5` FOREIGN KEY (`speciesId`) REFERENCES `species` (`speciesId`) ON DELETE CASCADE

# table affymetrixChip
  PRIMARY KEY (`bgeeAffymetrixChipId`),
  UNIQUE KEY `affymetrixChipId` (`affymetrixChipId`,`microarrayExperimentId`),
  UNIQUE KEY `conditionId` (`conditionId`,`bgeeAffymetrixChipId`),
  KEY `microarrayExperimentId` (`microarrayExperimentId`),
  KEY `chipTypeId` (`chipTypeId`),
  CONSTRAINT `affymetrixChip_ibfk_1` FOREIGN KEY (`microarrayExperimentId`) REFERENCES `microarrayExperiment` (`microarrayExperimentId`) ON DELETE CASCADE,
  CONSTRAINT `affymetrixChip_ibfk_2` FOREIGN KEY (`chipTypeId`) REFERENCES `chipType` (`chipTypeId`) ON DELETE SET NULL,
  CONSTRAINT `affymetrixChip_ibfk_3` FOREIGN KEY (`conditionId`) REFERENCES `cond` (`conditionId`) ON DELETE CASCADE

#table affymetrixProbeset
  PRIMARY KEY (`bgeeAffymetrixChipId`,`affymetrixProbesetId`),
  UNIQUE KEY `bgeeAffymetrixChipId_2` (`bgeeAffymetrixChipId`,`affymetrixProbesetId`,`bgeeGeneId`),
  KEY `bgeeAffymetrixChipId` (`bgeeAffymetrixChipId`,`expressionId`,`bgeeGeneId`,`normalizedSignalIntensity`),
  KEY `expressionId` (`expressionId`),
  KEY `bgeeGeneId` (`bgeeGeneId`,`bgeeAffymetrixChipId`,`affymetrixProbesetId`),
  CONSTRAINT `affymetrixProbeset_ibfk_1` FOREIGN KEY (`bgeeAffymetrixChipId`) REFERENCES `affymetrixChip` (`bgeeAffymetrixChipId`) ON DELETE CASCADE,
  CONSTRAINT `affymetrixProbeset_ibfk_2` FOREIGN KEY (`bgeeGeneId`) REFERENCES `gene` (`bgeeGeneId`) ON DELETE CASCADE,
  CONSTRAINT `affymetrixProbeset_ibfk_3` FOREIGN KEY (`expressionId`) REFERENCES `expression` (`expressionId`) ON DELETE SET NULL

UPDATE 2 :

Sizes of the tables as result of SHOW TABLE STATUS

| Name                                                | Engine | Version | Row_format | Rows       | Avg_row_length | Data_length   | Max_data_length | Index_length | Data_free    | Auto_increment | Create_time         | Update_time         | Check_time | Collation       | Checksum | Create_options | Comment     
| affymetrixChip                                      | InnoDB |      10 | Dynamic    |      13020 |            122 |       1589248 |               0 |      1097728 |      2097152 |         104523 | 2022-12-02 13:23:15 | NULL                | NULL       | utf8_general_ci |     NULL |                |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          |
| affymetrixProbeset                                  | InnoDB |      10 | Dynamic    |  312237020 |             93 |   29169287168 |               0 |  41072721920 |      6291456 |           NULL | 2022-10-31 09:34:15 | NULL                | NULL       | utf8_general_ci |     NULL |                |  
| cond                                                | InnoDB |      10 | Dynamic    |      44578 |            106 |       4734976 |               0 |      6815744 |      2097152 |          44663 | 2022-12-02 13:19:09 | NULL                | NULL       | utf8_general_ci |     NULL |                |  

The query below gives 4889 results

SELECT COUNT(*) FROM cond WHERE speciesId = 9606;

The query below gives 5452 results

SELECT COUNT(bgeeAffymetrixChipId) FROM affymetrixChip INNER JOIN cond ON cond.conditionId = affymetrixChip.conditionId WHERE cond.speciesId = 9606;

Answer 1

Don't use IN ( SELECT ... ); it often is poorly optimized. Switch to a JOIN.

SELECT  probe.*
    FROM  affymetrixProbeset AS probe
    JOIN  affymetrixChip AS chip
             ON  probe.bgeeAffymetrixChipId = chip.bgeeAffymetrixChipId
    JOIN  cond  ON chip.conditionId = cond.conditionId
    WHERE  cond.speciesId = 9606
    order by  probe.affymetrixProbesetId, probe.bgeeGeneId
    limit  10;

And have these indexes:

probe:  INDEX(bgeeAffymetrixChipId, affymetrixProbesetId, bgeeGeneId)
chip:  INDEX(conditionId,  bgeeAffymetrixChipId)
cond:  INDEX(speciesId, conditioned)

(I am hoping this will make the combined query as fast as the 2-query formulation, thereby making your question moot.)

Part of the problem may be hauling around * for lots of rows, then sorting them, and finally delivering only 10. If this formulation is not efficient enough, I can suggest a way to avoid fetching * more than 10 times, but it involves a different 'derived' table.

To discuss further, please provide SHOW CREATE TABLE.

On indexes (This section of my Answer probably does not address the original problem.)

# table cond
  PRIMARY KEY                        (`conditionId`),
  UNIQUE KEY `speciesId` (`speciesId`,`conditionId`),
      -- Since the PK contains all the columns of the above Unique, it is automatically unique
      -- recommend degrading to INDEX

# table affymetrixChip
  PRIMARY KEY                            (`bgeeAffymetrixChipId`),
  UNIQUE KEY `conditionId` (`conditionId`,`bgeeAffymetrixChipId`),
         --  See above

#table affymetrixProbeset
  PRIMARY KEY (`bgeeAffymetrixChipId`,`affymetrixProbesetId`),
  UNIQUE KEY `bgeeAffymetrixChipId_2`
              (`bgeeAffymetrixChipId`,`affymetrixProbesetId`,`bgeeGeneId`),
       -- The first 2 columns of this UNIQUE exactly match the PK.
       --  The key is completely useless; DROP it.

I doubt if I have ever seen a valid reason to have 3 UNIQUE keys (including the PK) in a table. Rethink whether they are valid. Also, consider removing any AUTO_INCREMENT when there is a UNIQUE that could be used for the PK.

(Caution: UNIQUE allows non-NULL columns; PK does not.)

More formulations

SELECT  probe.*
    FROM  
        ( SELECT  chip.bgeeAffymetrixChipId
            FROM  affymetrixChip AS chip
            INNER JOIN  cond  ON chip.conditionId = cond.conditionId
            WHERE  cond.speciesId = 9606 
        ) AS x
    JOIN  affymetrixProbeset AS probe USING(bgeeAffymetrixChipId)
    ORDER BY  probe.affymetrixProbesetId, probe.bgeeGeneId
    limit  10;

or, to avoid hauling around "*":

SELECT  probe.*
    FROM  
        ( SELECT  p.affymetrixProbesetId, p.bgeeGeneId
            FROM  
                ( SELECT  chip.bgeeAffymetrixChipId
                    FROM  affymetrixChip AS chip
                    INNER JOIN  cond  ON chip.conditionId = cond.conditionId
                    WHERE  cond.speciesId = 9606 
                ) AS x
            JOIN  affymetrixProbeset AS p USING(bgeeAffymetrixChipId)
            ORDER BY  p.affymetrixProbesetId, p.bgeeGeneId
            limit  10   -- needed only here
        ) AS y
    JOIN  affymetrixProbeset AS probe
             USING (affymetrixProbesetId, bgeeGeneId)
    ORDER BY  p.affymetrixProbesetId, p.bgeeGeneId;  -- Yes, repeated