Re: [Maria-developers] Please look at and advise about MDEV-3801
On 10/19/2012 12:43 PM, Timour Katchaounov wrote:
Timour, The following patch fixes this problem Regards, Igor. === modified file 'sql/sql_select.cc' --- sql/sql_select.cc 2012-10-10 05:21:22 +0000 +++ sql/sql_select.cc 2012-10-20 05:49:13 +0000 @@ -8981,7 +8981,7 @@ void JOIN::drop_unused_derived_keys() JOIN_TAB *tab; for (tab= first_linear_tab(this, WITHOUT_CONST_TABLES); tab; - tab= next_linear_tab(this, tab, WITHOUT_BUSH_ROOTS)) + tab= next_linear_tab(this, tab, WITH_BUSH_ROOTS)) { TABLE *table=tab->table; MariaDB [test]> set @@tmp_table_size=16384; ERROR 2006 (HY000): MySQL server has gone away No connection. Trying to reconnect... Connection id: 1 Current database: test Query OK, 0 rows affected (5.48 sec) MariaDB [test]> set @@tmp_table_size=16384; Query OK, 0 rows affected (0.00 sec) MariaDB [test]> EXPLAIN SELECT t1.deal_id FROM table_e AS t1 JOIN table_d AS t10 ON (t1.deal_id = t10.deal_id) WHERE t1.deal_id IN (SELECT * FROM (SELECT DISTINCT t1.deal_id FROM table_e AS t1 JOIN table_d AS t2 ON (t1.deal_id = t2.deal_id) JOIN table_c_s AS t3 ON (t2.deal_id = t3.deal_id) AND (t2.feid = t3.feid) JOIN table_f AS t4 ON t3.exchange = t4.exchange) tU) limit 10; +----+--------------+-------------+--------+-------------------------------------------+----------+---------+------------------------------+------+-----------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+--------------+-------------+--------+-------------------------------------------+----------+---------+------------------------------+------+-----------------------+ | 1 | PRIMARY | <subquery2> | ALL | distinct_key | NULL | NULL | NULL | 8488 | | | 1 | PRIMARY | t1 | eq_ref | PRIMARY | PRIMARY | 4 | tU.deal_id | 1 | Using index | | 1 | PRIMARY | t10 | ref | PRIMARY,full_id | PRIMARY | 4 | tU.deal_id | 1 | Using index | | 2 | MATERIALIZED | <derived3> | ALL | NULL | NULL | NULL | NULL | 8488 | | | 3 | DERIVED | t4 | system | PRIMARY | NULL | NULL | NULL | 1 | Using temporary | | 3 | DERIVED | t3 | ref | PRIMARY,feid,exchange,deal_feid,feid_date | exchange | 3 | const | 8488 | | | 3 | DERIVED | t1 | eq_ref | PRIMARY | PRIMARY | 4 | test.t3.deal_id | 1 | Using index | | 3 | DERIVED | t2 | eq_ref | PRIMARY,full_id | PRIMARY | 8 | test.t3.deal_id,test.t3.feid | 1 | Using index; Distinct | +----+--------------+-------------+--------+-------------------------------------------+----------+---------+------------------------------+------+-----------------------+ 8 rows in set (4.22 sec) MariaDB [test]> SELECT t1.deal_id FROM table_e AS t1 JOIN table_d AS t10 ON (t1.deal_id = t10.deal_id) WHERE t1.deal_id IN (SELECT * FROM (SELECT DISTINCT t1.deal_id FROM table_e AS t1 JOIN table_d AS t2 ON (t1.deal_id = t2.deal_id) JOIN table_c_s AS t3 ON (t2.deal_id = t3.deal_id) AND (t2.feid = t3.feid) JOIN table_f AS t4 ON t3.exchange = t4.exchange) tU) limit 10; +---------+ | deal_id | +---------+ | 30 | | 82 | | 83 | | 108 | | 155 | | 156 | | 162 | | 184 | | 186 | | 195 | +---------+ 10 rows in set (10.62 sec)
participants (1)
-
Igor Babaev