10.3 was a really good, solid, predictable release. Beyond 10.4 I have seen a lot of weirdness which is why I have been avoiding it wherever reasonably possible. Additionally, version inflation on the whole leads to performance deflation, even without execution plan differences. This has been the case since the dawn of time. So if 10.3 works well for you and it isn't in an untrusted environment, I would stick with it while you can. On Mon, Oct 30, 2023 at 1:29 PM Ivan Krylov <krylov.r00t@gmail.com> wrote:
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