Hi Robert, #1: OS I've only tested on Linux. But I was using the source-code of MariaDB so I assume the effect will happen on Windows too. #2: SQL-statement this is the statement I've used for my test: MariaDB [TestOpt]> select SQL_NO_CACHE B.PZN, B.ArtikelText from TestBig B where exists ( select 1 from TestSmall A where A.Hersteller = '00020' and A.PZN = B.PZN and Id = 1) and B.Hersteller = '36367'; It's a silly statement but it demonstrates the effect.
You will find more details in my text: http://augustq.blogspot.com/2017 /01/subselect-execution-3.html #3: optimizer_switch here it is (polished it a bit): MariaDB [(none)]> select @@optimizer_switch; +-----------------------------------------------+ | @@optimizer_switch | +-----------------------------------------------+ | index_merge=on, index_merge_union=on, index_merge_sort_union=on, index_merge_intersection=on, index_merge_sort_intersection=off, engine_condition_pushdown=off, index_condition_pushdown=on, derived_merge=on, derived_with_keys=on, firstmatch=on, loosescan=on,materialization=on, in_to_exists=on, semijoin=on, partial_match_rowid_merge=on, partial_match_table_scan=on, subquery_cache=on, mrr=off, mrr_cost_based=off, mrr_sort_keys=off, outer_join_with_cache=on, semijoin_with_cache=on, join_cache_incremental=on, join_cache_hashed=on, join_cache_bka=on, optimize_join_buffer_size=off, table_elimination=on, extended_keys=on, exists_to_in=off | +-----------------------------------------------+ 1 row in set (0.00 sec) MariaDB [(none)]> I hope I did not introduce any errors. AugustQ Am Sonntag, den 29.01.2017, 15:22 -0200 schrieb Roberto Spadim: > hum... did you checked only linux version, or windows too? > could you provide a SQL example? > > 2017-01-29 14:30 GMT-02:00 AugustQ <augu...@gmx.net>: > > > > Hi, > > > > by playing with the code I think I found something interesting. > > > > My environment: MariaDB 10.0.10, MyISAM-engine > > > > I played with a table-scan, no index is defined on this table. When > > I > > execute a SQL-statement that forces the server to do a second > > table-scan on > > a table this 2nd table-scan will be slow. > > > > The reason for this behaviour is the usage of a buffer: during the > > 1st scan > > this buffer is filled, used and filled again until the whole table > > is > > processed. At the end of the 1st scan it contains the last bytes of > > the > > file. When a 2nd scan is started the reading of the table starts > > from the > > beginning of the file but the buffer and all associated variables > > are not > > reset: the buffer still contains the bytes from the end of the > > file, the > > request cannot be fulfilled by the buffer so the request has to be > > handled > > by reading the bytes directly from the file using the read()- > > function of > > the Std-library. This takes much more time then simply copying the > > bytes > > from the internal buffer. > > > > My idea is: somewhere in the code this situation must be detected > > and the > > buffer (and all associated variables) reset to initial values. > > reinit_io_cache() looks like the right candidate for this. > > > > I assume the engine Maria will show the same effect (I didn't check > > other > > engines). > > > > Some questions: is my observation correct? Do you think this needs > > to be > > fixed? Or is there no further development on this part of the code? > > > > Thanks > > > > Regards > > AugustQ > > > > PS: you will find more details on this topic here: > > http://augustq.blogspot.com/2017/01/subselect-execution-3.html > > > > > > _______________________________________________ > > Mailing list: https://launchpad.net/~maria-developers > > Post to : maria-developers@lists.launchpad.net > > Unsubscribe : https://launchpad.net/~maria-developers > > More help : https://help.launchpad.net/ListHelp > > > >
signature.asc
Description: This is a digitally signed message part
_______________________________________________ Mailing list: https://launchpad.net/~maria-developers Post to : maria-developers@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-developers More help : https://help.launchpad.net/ListHelp
