maria-developers team mailing list archive

[AugustQ <augustq@xxxxxxx>]

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@xxxxxxx>:
> > 
> > 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@xxxxxxxxxxxxxxxxxxx
> > Unsubscribe : https://launchpad.net/~maria-developers
> > More help   : https://help.launchpad.net/ListHelp
> > 
> 
>

Attachment: signature.asc
Description: This is a digitally signed message part