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 <augustq@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