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 <[email protected]> wrote: > > On Mon, 30 Oct 2023 08:10:02 +0200 > Gordan Bobic <[email protected]> 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 _______________________________________________ discuss mailing list -- [email protected] To unsubscribe send an email to [email protected]
