On Sun, Oct 29, 2023 at 5:03 PM Ivan Krylov via discuss <[email protected]> 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. _______________________________________________ discuss mailing list -- [email protected] To unsubscribe send an email to [email protected]
