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. Reasoning behind my approach was that secondary keys point at the PK, and the table is clustered in PK. So what I proposed should have avoided the additional key dereference that might have save about half of the time. So if explicit vs. implicit PK makes a 10x difference, and it is not due to buffer pool being hot vs. cold, that is most definitely a critical performance bug you just stumbled upon. On Sun, 29 Oct 2023, 21:08 Ivan Krylov via discuss, < discuss@lists.mariadb.org> wrote:

On Sun, 29 Oct 2023 18:12:53 +0200 Gordan Bobic wrote:

...

ALTER TABLE mol_trans DROP INDEX spid_flag_wl, ADD COLUMN id int unsigned auto_increment, ADD PRIMARY KEY (species_id, flag, wl_vac, id);

I wasn't able to add a primary key like this. My version of MariaDB only allows the id column in the beginning of the compound primary key. Additionally, trying to add a primary key to an existing table results in an error message complaining about the index for the table being corrupted (and rolling back the implicit transaction).

I tried recreating the table with PRIMARY KEY (species_id, flag, wl_vac, upper_id, lower_id). By itself, the combination of upper_id and lower_id must be unique, and it's our mistake that we didn't properly declare them as foreign keys into a different table. Unfortunately, this didn't improve the performance.

What _did_ improve performance was creating the id column with the type INT UNSIGNED AUTO_INCREMENT and setting it to be the primary key. With the index spid_flag_wl(species_id, flag, wl_vac) recreated, the EXPLAIN output now looks a bit differently:

id: 1 select_type: SIMPLE table: mtr type: range possible_keys: spid_flag_wl key: spid_flag_wl key_len: 16 ref: NULL rows: 5487882 Extra: Using index condition

...and I get my 3024559 rows in slightly more than 6 seconds.

So many thanks for giving me a pointer in the direction that eventually helped solve my problem, even if I don't fully understand why it works. Is the lesson here to always create a synthetic primary key for a table?

I've also tried recreating the table with the "natural" primary key of (upper_id, lower_id), but it takes much longer to reinsert the rows (it still isn't complete after tens of minutes, and previously reinsert would be done in just a few minutes), probably because the rows don't go in the natural order by upper_id and lower_id at all.

-- Best regards, Ivan _______________________________________________ discuss mailing list -- discuss@lists.mariadb.org To unsubscribe send an email to discuss-leave@lists.mariadb.org

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 wrote:

On Mon, 30 Oct 2023 08:10:02 +0200 Gordan Bobic 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

I have to say that surprises me. MyISAM became slower even on read-only workloads around MySQL 5. One of the reasons was that MyISAM uses the OS page cache for data caching and only caches index data internally. Which means that unless you have a covering index, you end up with more context switching to retrieve the row data. But maybe InnoDB has since been "enhanced" to the point where it is no longer outright faster on every workload. On Wed, Nov 1, 2023 at 12:14 PM Ivan Krylov via discuss wrote:

В Tue, 31 Oct 2023 11:45:52 +0200 Vassilis Virvilis пишет:

...

If you change the table format to MyISAM do you get better performance?

I almost missed your message, and that would have been a shame, because if I change the table format to MyISAM, I get the query results in ~3.5 seconds, which is faster than any other result I've been getting with MariaDB. Many thanks!

This is the largest table in our database, but it's also the easiest to recreate, so we might just switch it to MyISAM with no apparent downsides.

-- Best regards, Ivan _______________________________________________ discuss mailing list -- discuss@lists.mariadb.org To unsubscribe send an email to discuss-leave@lists.mariadb.org

I would say that is definitely worthy of a bug report. A really interesting bug, too. On Tue, Oct 31, 2023 at 1:15 PM Ivan Krylov via discuss wrote:

To summarise:

* "Sending data" is a red herring. MariaDB sets stage_sending_data before calling do_select(), which may perform a lot of additional work _besides_ serialising and sending data to the client.

* Various sacrifices to the query optimiser spirit, such as creating an additional synthetic primary key column, can be made, but there is no good reason for them to be working. InnoDB has a perfectly good hidden rowid that it should be able to use.

* There is a difference between a query plan that seems to automatically use an index and a query plan that is _forced_ to use an index:

MariaDB [spmodel]> analyze 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 *************************** 1. row *************************** id: 1 select_type: SIMPLE table: mtr type: ref possible_keys: spid_flag_wl key: spid_flag_wl key_len: 7 ref: const,const rows: 14025100 r_rows: 28417908.00 filtered: 100.00 r_filtered: 10.64 Extra: Using where 1 row in set (1 min 48,719 sec)

MariaDB [spmodel]> analyze select -> mtr.prob, mtr.lower_id, mtr.upper_id -> from mol_trans mtr -> force index(spid_flag_wl) -- The only difference! -> 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 *************************** 1. row *************************** id: 1 select_type: SIMPLE table: mtr type: range possible_keys: spid_flag_wl key: spid_flag_wl key_len: 16 ref: NULL rows: 6260712 r_rows: 3024559.00 filtered: 100.00 r_filtered: 100.00 Extra: Using where 1 row in set (11,086 sec)

The latter can be much faster than the former. Since FORCE INDEX improves the performance of this query on everything ranging from MariaDB-10.3.39-0+deb10u1 to MySQL 5.6 on Windows 10, we'll be using this.

* Is any of this worth reporting as a query optimiser bug? The contents of the table are the result of preprocessing a CC-BY-SA-4.0 dataset, so the only thing stopping me from reporting this is the dump being 2 GB in size. My perf-fu is probably not enough to find the source of the problem (if it is a problem) by myself.

* The last but not the least, thanks to Gordan Bobic for valuable advice!

-- Best regards, Ivan _______________________________________________ discuss mailing list -- discuss@lists.mariadb.org To unsubscribe send an email to discuss-leave@lists.mariadb.org