On Sun, Oct 29, 2023 at 5:03 PM Ivan Krylov via discuss <discuss@lists.mariadb.org> wrote:
Dear MariaDB users,
(I've asked the same question at <https://dba.stackexchange.com/q/332557>, so feel free to ignore this one if you've already seen the other.)
Our scientific application needs to store and query fundamental parameters for a number of molecules. There are 2 to 28 million rows per molecule, but the number of molecules is expected to stay small (currently 4). Here's our schema:
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=utf8 COLLATE=utf8_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)
(The partitions are here to make it easier to drop a whole molecule if needed, which would otherwise be a painful large DELETE.)
I'm using the admittedly old MariaDB 10.3.39 from Debian Buster for tests (connecting via the UNIX domain socket using the command line client), but we've been seeing the same problem on MySQL 5.6 and MariaDB 10.11 on Windows 10.
The following query currently takes 1m 7s on my computer, as measured by running `time echo "$QUERY" | mysql "$DATABASE" >/dev/null`:
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;
Try this: 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); That should avoid a secondary key dereference and shave maybe half of the execution time off.