On Mon, 30 Oct 2023 08:10:02 +0200 Gordan Bobic <gordan.bobic@gmail.com> wrote:
That is really weird. Just having a visible vs. invisible PK should not have made any difference at all. In InnoDB there is always a PK, if you don't define one, an invisible 48-bit integer one will be defined for you.
Thank you for confirming this! The strangeness continues. I've built MariaDB-11.1.2 and transferred the SQL dump of the table in question there. With the original table definition, I get the following query plan: CREATE TABLE `mol_trans` ( `species_id` int(11) DEFAULT NULL, `wl_vac` double DEFAULT NULL, `upper_id` int(11) DEFAULT NULL, `lower_id` int(11) DEFAULT NULL, `prob` double DEFAULT NULL, `flag` tinyint(4) DEFAULT NULL, KEY `spid_flag_wl` (`species_id`,`flag`,`wl_vac`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci PARTITION BY LIST (`species_id`) (PARTITION `CaO` VALUES IN (6115) ENGINE = InnoDB, PARTITION `CN3` VALUES IN (6121) ENGINE = InnoDB, PARTITION `CN2` VALUES IN (6119) ENGINE = InnoDB, PARTITION `AlO` VALUES IN (6109) ENGINE = InnoDB); explain select mtr.prob, mtr.lower_id, mtr.upper_id from mol_trans mtr where ( mtr.species_id=6115 and mtr.wl_vac > 766.0 and mtr.wl_vac < 883.0 and mtr.flag = 1 ) order by mtr.wl_vac\G id: 1 select_type: SIMPLE table: mtr type: ALL possible_keys: spid_flag_wl key: NULL key_len: NULL ref: NULL rows: 26559953 Extra: Using where; Using filesort Note that it doesn't even use the index now. This SELECT takes upwards of 1 minute to complete. If I remove the partitions *and* add an explicit synthetic primary key, I get a query plan that uses the index: CREATE TABLE `mol_trans_3` ( `species_id` int(11) NOT NULL, `wl_vac` double DEFAULT NULL, `upper_id` int(11) DEFAULT NULL, `lower_id` int(11) DEFAULT NULL, `prob` double DEFAULT NULL, `flag` tinyint(4) DEFAULT NULL, `id` int(10) unsigned NOT NULL AUTO_INCREMENT, PRIMARY KEY (`id`), KEY `spid_flag_wl` (`species_id`,`flag`,`wl_vac`) ) ENGINE=InnoDB AUTO_INCREMENT=81305699 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci id: 1 select_type: SIMPLE table: mtr type: range possible_keys: spid_flag_wl key: spid_flag_wl key_len: 15 ref: NULL rows: 6083086 Extra: Using index condition This query just executed on my machine in 21 seconds. Finally, if I go back to the original table and force the use of the index, I get a third query plan: explain select mtr.prob, mtr.lower_id, mtr.upper_id from mol_trans mtr force index (spid_flag_wl) where ( mtr.species_id=6115 and mtr.wl_vac > 766.0 and mtr.wl_vac < 883.0 and mtr.flag = 1 ) order by mtr.wl_vac\G id: 1 select_type: SIMPLE table: mtr type: range possible_keys: spid_flag_wl key: spid_flag_wl key_len: 16 ref: NULL rows: 6266474 Extra: Using where ...and the query now completes in 28 seconds. ANALYZE TABLE mol_trans PERSISTENT FOR ALL doesn't seem to lead to further improvements. Anything else I could do to speed this up? With MariaDB-10.3.39-0+deb10u1, I somehow get the result in 10-20 seconds using the mol_trans_3 table, no matter whether the query specifies FORCE INDEX or not. -- Best regards, Ivan