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 -- [email protected]
To unsubscribe send an email to [email protected]
