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]
