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 <discuss@lists.mariadb.org> 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 -- discuss@lists.mariadb.org To unsubscribe send an email to discuss-leave@lists.mariadb.org