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