From: Ole John Aske Date: October 21 2010 8:31am Subject: bzr commit into mysql-5.1-telco-7.0-spj-scan-vs-scan branch (ole.john.aske:3322) List-Archive: http://lists.mysql.com/commits/121481 Message-Id: <20101021083156.89B7D21D@fimafeng09.norway.sun.com> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="===============7265386631446262925==" --===============7265386631446262925== MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit Content-Disposition: inline #At file:///net/fimafeng09/export/home/tmp/oleja/mysql/mysql-5.1-telco-7.0-spj-scan-scan/ based on revid:ole.john.aske@stripped 3322 Ole John Aske 2010-10-21 spj-svs: Enhanced implementation of handling 'sorted indexscan(t1) -> index ref(t2)' We previously had to block pushing of scan childs if the root operation was an ordered index scan as we are not able to provide these result ordered through the root operation. This fix is now reverted - Instead we now allow these kind of queries to be pushed, but require the result to be written to temp. file where it is filesorted. We believe the overhead of this extra filesort to be far less than not pushing the query. modified: mysql-test/suite/ndb/r/ndb_join_pushdown.result sql/abstract_query_plan.cc sql/abstract_query_plan.h sql/ha_ndbcluster.cc sql/handler.h sql/sql_select.cc === modified file 'mysql-test/suite/ndb/r/ndb_join_pushdown.result' --- a/mysql-test/suite/ndb/r/ndb_join_pushdown.result 2010-10-21 07:50:44 +0000 +++ b/mysql-test/suite/ndb/r/ndb_join_pushdown.result 2010-10-21 08:31:51 +0000 @@ -3929,8 +3929,8 @@ join t1 as x2 on x1.a=x2.b join t1 as x3 on x2.a=x3.b order by x1.pk limit 70; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE x1 index NULL PRIMARY 4 NULL 10 -1 SIMPLE x2 ref ix1 ix1 5 test.x1.a 1 Parent of 2 pushed join@1; Using where +1 SIMPLE x1 index NULL PRIMARY 4 NULL 10 Parent of 3 pushed join@1; Using temporary; Using filesort +1 SIMPLE x2 ref ix1 ix1 5 test.x1.a 1 Child of pushed join@1; Using where 1 SIMPLE x3 ref ix1 ix1 5 test.x2.a 1 Child of pushed join@1; Using where select x1.pk,x1.a,x1.b from t1 as x1 join t1 as x2 on x1.a=x2.b @@ -4009,8 +4009,8 @@ pk a b 2 20 20 explain select * from t1 as x1, t1 as x2 where x1.a=x2.b and x1.b = 3; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE x1 ref ix1 ix1 5 const 10 Using where with pushed condition -1 SIMPLE x2 ref ix1 ix1 5 test.x1.a 1 Using where +1 SIMPLE x1 ref ix1 ix1 5 const 10 Parent of 2 pushed join@1; Using where with pushed condition +1 SIMPLE x2 ref ix1 ix1 5 test.x1.a 1 Child of pushed join@1; Using where select * from t1 as x1, t1 as x2 where x1.a=x2.b and x1.b = 3; pk a b pk a b drop table t1; @@ -4071,10 +4071,10 @@ counter_name spj_counts_at_end.val - spj CONST_PRUNED_RANGE_SCANS_RECEIVED 6 LOCAL_TABLE_SCANS_SENT 196 PRUNED_RANGE_SCANS_RECEIVED 16 -RANGE_SCANS_RECEIVED 205 +RANGE_SCANS_RECEIVED 203 READS_NOT_FOUND 405 READS_RECEIVED 61 -SCAN_ROWS_RETURNED 63643 +SCAN_ROWS_RETURNED 63863 TABLE_SCANS_RECEIVED 196 select sum(spj_counts_at_end.val - spj_counts_at_startup.val) as 'LOCAL+REMOTE READS_SENT' from spj_counts_at_end, spj_counts_at_startup @@ -4086,15 +4086,15 @@ LOCAL+REMOTE READS_SENT drop table spj_counts_at_startup; drop table spj_counts_at_end; scan_count -2003 +2000 pruned_scan_count 7 sorted_scan_count -44 +7 pushed_queries_defined -341 +343 pushed_queries_dropped 11 pushed_queries_executed -265 +264 set ndb_join_pushdown = @save_ndb_join_pushdown; === modified file 'sql/abstract_query_plan.cc' --- a/sql/abstract_query_plan.cc 2010-09-24 07:29:43 +0000 +++ b/sql/abstract_query_plan.cc 2010-10-21 08:31:51 +0000 @@ -449,16 +449,6 @@ namespace AQP {} /** - @return True if sorted results are expetced from this operation. - */ - bool Table_access::is_sorted() const - { - return get_join_tab()->type == JT_NEXT || - (get_join_tab()->type == JT_REF && get_join_tab()->sorted) || - (get_join_tab()->type == JT_REF_OR_NULL && get_join_tab()->sorted); - } - - /** @param plan Iterate over fields within this plan. @param field_item Iterate over Item_fields equal to this. */ === modified file 'sql/abstract_query_plan.h' --- a/sql/abstract_query_plan.h 2010-09-24 07:29:43 +0000 +++ b/sql/abstract_query_plan.h 2010-10-21 08:31:51 +0000 @@ -205,8 +205,6 @@ namespace AQP void dbug_print() const; - bool is_sorted() const; - private: /** Backref. to the Join_plan which this Table_access is part of */ === modified file 'sql/ha_ndbcluster.cc' --- a/sql/ha_ndbcluster.cc 2010-10-21 08:14:23 +0000 +++ b/sql/ha_ndbcluster.cc 2010-10-21 08:31:51 +0000 @@ -1302,13 +1302,6 @@ ha_ndbcluster::make_pushed_join(AQP::Joi DBUG_PRINT("info", ("Table %d not a pushable access type", join_cnt)); continue; } - if (join_root->is_sorted() && !is_lookup_operation(child_type)) - { - // Sorted scan cannot have scan descendant. - DBUG_PRINT("info", ("Table %d is scanned. Therefore it cannot be pushed" - " as a descendant of a sorted scan", join_cnt)); - continue; - } if (!context.field_ref_is_join_pushable(join_tab, join_items, join_parent)) { DBUG_PRINT("info", ("Table %d not REF-joined, not pushable", join_cnt)); @@ -1631,8 +1624,7 @@ private: */ bool ha_ndbcluster::check_if_pushable(const NdbQueryOperationTypeWrapper& type, - uint idx, - bool rootSorted) const + uint idx, bool needSorted) const { if (m_pushed_join == NULL) { @@ -1685,6 +1677,12 @@ ha_ndbcluster::check_if_pushable(const N case NdbQueryOperationDef::TableScan: DBUG_ASSERT (idx==MAX_KEY); + if (needSorted) + { + DBUG_PRINT("info", ("TableScan access not not be provied as sorted result. ", + "Therefore, join cannot be pushed.")); + return FALSE; + } break; case NdbQueryOperationDef::OrderedIndexScan: @@ -1692,30 +1690,12 @@ ha_ndbcluster::check_if_pushable(const N // DBUG_ASSERT(m_index[idx].index == expected_index); if (m_index[idx].index != expected_index) { - DBUG_PRINT("info", ("Actual index %s differs from expected index %s." + DBUG_PRINT("info", ("Actual index %s differs from expected index %s. " "Therefore, join cannot be pushed.", m_index[idx].index->getName(), expected_index->getName())); return FALSE; } - // Check that we do not have a sorted scan with sub scans. - if (rootSorted) - { - const NdbQueryDef& query_def = m_pushed_join->get_query_def(); - for (uint i= 1; i < query_def.getNoOfOperations(); i++) - { - const NdbQueryOperationTypeWrapper& child_type= - query_def.getQueryOperation(i)->getType(); - if (child_type == NdbQueryOperationDef::TableScan || - child_type == NdbQueryOperationDef::OrderedIndexScan) - { - DBUG_PRINT("info", ("If the root operation is a sorted scan, then " - "there may not be scan children. This join " - "cannot be pushed.")); - return FALSE; - } - } - } break; default: @@ -1902,6 +1882,46 @@ ha_ndbcluster::test_push_flag(enum ha_pu } DBUG_RETURN(false); + case HA_PUSH_NO_ORDERED_INDEX: + { + if (!m_pushed_join) + { + DBUG_RETURN(true); + } + const NdbQueryDef& query_def = m_pushed_join->get_query_def(); + const NdbQueryOperationTypeWrapper& root_type= + query_def.getQueryOperation(0U)->getType(); + + /** + * Primary key/ unique key lookup is always 'ordered' wrt. itself. + */ + if (root_type == NdbQueryOperationDef::PrimaryKeyAccess || + root_type == NdbQueryOperationDef::UniqueIndexAccess) + { + DBUG_RETURN(false); + } + + /** + * Ordered index scan can be provided as an ordered resultset iff + * it has no child scans. + */ + if (root_type == NdbQueryOperationDef::OrderedIndexScan) + { + for (uint i= 1; i < query_def.getNoOfOperations(); i++) + { + const NdbQueryOperationTypeWrapper& child_type= + query_def.getQueryOperation(i)->getType(); + if (child_type == NdbQueryOperationDef::TableScan || + child_type == NdbQueryOperationDef::OrderedIndexScan) + { + DBUG_RETURN(true); + } + } + DBUG_RETURN(false); + } + DBUG_RETURN(true); + } + default: DBUG_ASSERT(0); DBUG_RETURN(false); === modified file 'sql/handler.h' --- a/sql/handler.h 2010-10-15 21:04:14 +0000 +++ b/sql/handler.h 2010-10-21 08:31:51 +0000 @@ -441,6 +441,11 @@ enum ha_push_flag { within this pushed join */ ,HA_PUSH_MULTIPLE_DEPENDENCY + + /* Handler is unable to return the result in sorted order using an + ordered index on the parent operation. + */ + ,HA_PUSH_NO_ORDERED_INDEX }; /** === modified file 'sql/sql_select.cc' --- a/sql/sql_select.cc 2010-10-21 07:50:44 +0000 +++ b/sql/sql_select.cc 2010-10-21 08:31:51 +0000 @@ -1702,21 +1702,23 @@ make_pushed_join(THD *thd, JOIN *join) } } - /* If we just pushed a join containing an ORDER BY and/or GROUP BY clause, + /* If we just pushed a join containing an ORDER BY and/or a GROUP BY clause, * we have to ensure that we either can skip the sort by scanning an ordered index, * or write to a temp. table later being filesorted. */ if (join->const_tables < join->tables && join->join_tab[join->const_tables].table->file->is_parent_of_pushed_join()) { + const handler *ha=join->join_tab[join->const_tables].table->file; + if (join->group_list && join->simple_group && - !plan.group_by_filesort_is_skippable()) + (!plan.group_by_filesort_is_skippable() || ha->test_push_flag(HA_PUSH_NO_ORDERED_INDEX))) { join->need_tmp= 1; join->simple_order= join->simple_group= 0; } else if (join->order && join->simple_order && - !plan.order_by_filesort_is_skippable()) + (!plan.order_by_filesort_is_skippable() || ha->test_push_flag(HA_PUSH_NO_ORDERED_INDEX))) { join->need_tmp= 1; join->simple_order= join->simple_group= 0; @@ -12111,8 +12113,8 @@ join_read_key_unlock_row(st_join_table * /** Read a table *assumed* to be included in execution of a pushed join. - This is the counterpart of join_read_key() for child tables in a - pushed join. + This is the counterpart of join_read_key() / join_read_always_key() + for child tables in a pushed join. When the table access is performed as part of the pushed join, all 'linked' child colums are prefetched together with the parent row. @@ -12139,6 +12141,7 @@ join_read_linked_first(JOIN_TAB *tab) TABLE *table= tab->table; DBUG_ENTER("join_read_linked_first"); + DBUG_ASSERT(!tab->sorted); // Pushed child can't be sorted if (!table->file->inited) table->file->ha_index_init(tab->ref.key, tab->sorted); @@ -13973,6 +13976,7 @@ check_reverse_order: DBUG_RETURN(0); // Reverse sort not supported } select->quick=tmp; + DBUG_ASSERT(select->quick->sorted); } } else if (tab->type != JT_NEXT && tab->type != JT_REF_OR_NULL && --===============7265386631446262925== MIME-Version: 1.0 Content-Type: text/bzr-bundle; charset="us-ascii"; name="bzr/ole.john.aske@stripped" Content-Transfer-Encoding: 7bit Content-Disposition: inline # Bazaar merge directive format 2 (Bazaar 0.90) # revision_id: ole.john.aske@stripped\ # xxwd10essm8km423 # target_branch: file:///net/fimafeng09/export/home/tmp/oleja/mysql\ # /mysql-5.1-telco-7.0-spj-scan-scan/ # testament_sha1: 7d5577a79cdb7a0f9b18b97a01b676a73b39cb99 # timestamp: 2010-10-21 10:31:56 +0200 # source_branch: bzr+ssh://oaske@stripped/bzrroot/server\ # /mysql-5.1-telco-7.0-spj/ # base_revision_id: ole.john.aske@stripped\ # l08sw3e89q5wwixz # # Begin bundle IyBCYXphYXIgcmV2aXNpb24gYnVuZGxlIHY0CiMKQlpoOTFBWSZTWScDOmwABuL/gHVwAQJz//// f///6r////5gDab55j23tvdOg60A9ArejDti6wKAUHszrAq6aV4ShUbRNR6hqe0RoTYSaeyJhMmg hgBomCMaAJphKmpkU9Mymk9TahmieoADQAGmgAAAAAAaBGmoTFT9TRMnqemKPU9QAaDTIaAaAAAA ASJIApmSnjRGRPVD9TTTJojyg8miaPQTQGm1AaaDQ4GjRiDRpkwgxAYjE0aNGgDTTQAAABJEJoE0 ZANAp6amIGRoCmjZNQ00aGgAAZIQRDjPHDc4dJGUyq3q6qPBpqHQi6ZML4TLX/Fm6lS1JZWFrrui dRyK8tGwLO6SIjibNxODHR+mNuqAm34YieUHvjBgNYCQG4NvjC++BWY3aQ0pN/pIs2lqOLLlF2zX u2MMtzghsdGrViQ12coFyRJkubFDCIshf7pKESlRZHGNjNCLSqvjOU6qYtJ5Tq42aRJHJhJIkkkM wEHgdwl2b9I7mvdW3tzkG7tF+gsBNNobE2NgNto3v2AN5mk10YzCNTk2yNI4mcPUyHO6WQ5zVUz1 EF1pTOqrIbSrdUk5VwiTSDc2ODUyU6G6ymqLISc3OQN5vkqMhb6vTjzt0p6hwJr4yBichpjaIaMu V6H/NGgGOpbJxz0ohg2PkhpHiRq+5KJaViv8JcYKnM+O9/1doelsJMIPpIPPfKzNRX0y1Bez3cta WJRPJAVEpRr74smKi6NMokSdq0PJJRMRRa6xPWyb3X7bzw0G9TMubhukxXCzRaRMKOIdr8FjCACY TmxvxK5LFlDRg82lQmPuhH1Uw1B1YbjBbdq9qly7oELYY6V9F+NZDJqiRQYjFbw375tSK8p1yAga QTmrU9GqYwfl6/3kM7xwKw9FM4f4hUAojsTWCykx5jv2thJmM9BwDV9c03PAnRBB/Sdp+dMMXmaq mxeauFBqzs3YdhAckcw9yBeoXSdTdGMS3ZLxYCQ/RXCCB4R4joW4D3g+9t9DIHx32sqAUmNDvdT7 XeId/xtqUQFW0045qAV1qhFmIgJw/4xQda68amP0DJa1ONQs0VkLtGmoAUTk8vNmCcVISKhkh7o0 JUBJITE5xpIlIIB2lRQPIjQGkeBQQKydLEZkBgmMMCMRzkE5VBE3nMctBki0rXk85Io3GGYy21OM Ada7z5g6NHxGEGVqGyMYXxLI55wpLeGuQBTaXD+WoJDIS+TJCtuoYSNhn87AEsMdOyTmFVY0a6gs FbkeymFeNstzEypWlOskfuJKGUChzCsJzRJD6b7p4AAIMVoigitCE3QrTQwWIVLsHEqYIGGl1W0L D7ghnFw3ldfE0i2mLi0FJ3LgY5vwyona+xpS7eyQZ2hLZuypTq3xI0mAzEGvYXTAzKTJBM9tUNgw xAtpM1xQW1SsLS4qN0hFB56iotcqPvPTYigtowhXSPkJRtdps8GeEaEphQOI3lBEJp9gAw5EeZWO NEuahjAw1l1JV0wKjJIorJ3MiUjNI1EzUi4rPwPpC3ApRiiHf2G+2N2bYDuX3xjJ5Ws0ZzkYMDFB ImEAUkbmCZQnWKBgZJnJ0lZEURgpWhiNZMg5mImJZkLNBbQSLdZ4+kYnYgqoMS8oULdpacUd5rWh X3U6cKncjCUcaDeYmBSIM75tOSVd6KpmBWlqqtNBeF54rXKhjILJaji4TFBZvwmsJSc4YNH5Cm5V cekKpHdMr2CNb6qzS1hQXGiRPKxYNMcHNeQDUBIrMxzCovCbIsMxzYlkzSvyvRQ7wp4TCAoMUmV1 2XPWetFxnqInAutgUmXA8CJo80y8ao1ua8oF5AtUCoct4nTymBMN+RhsLLLoDnN8AZgBtgxWNRle ticcaqSo6WOYFZCIrrM9BWZ9RMgUnWaAC1F8bMsY52lFmzBeX0srSionSZZxNr1m0amRYxY4xW5E cXIRQECkFRjhjWhC0SIjIo2rGkcD94TDCnVIZuuJMhDVA8hIOB7+zJdlRUhq0NoyOYWNOBmglnYC UgHS50rBFNT0c4eppeGtEbNqToMvUiXHMUjBTNGGFGXWEYQZ5Cpn4BJCuohAD4CSndi7j2+8/gMN uO+3ATE2P1oJG7/g+YZKPYjs8mRHmCkMedHYFuOn/aoDPcigkKA9t5Q/Sg/HzEqgdUBpCC+bFsIM l52BuHt/pFiGDi0HI0kVgdxzhQXh9XryIPZOBbzC2sTEpN8LRks/L/qKYAwsFYLbCJg89GsmFwUG f79h0NpskNkF00tdp0niPZ9KCs1/cLYFlSOMnsvLgy2GP0bjqDyBYEDYG50NhZr+f19SX5S9bcRO mJl6veIjwVIqCa9Q6VMCmr5KQKWIxCJEQThKzkIRpy0DRgpS9suXDaGnAbO8RDxKIsDcKiq2+0rI x5NBLhGdXE2uspoziS1Xv6hmA2GUz06TExsQZnD7Pnl9o3s1CnA1ozuOKwD6yNsn8jBQoleFHpL0 B1UNH9LVNV0AUldNCupPQ50ycPVDhabxuEZm44jImcT9RQUDAMwTOg9B1jqszpUDoPrKkEjHAtcu sO2cwILEtpxbA9AWGBhZIyMoDyXJJ6jMzCjKucP0ysiXolsSxo0sKee4xVK/JQaT1BROAs623CFs n6n1+tn6yPmNQWieDRaxJjRGCgM4wuYmI49XnAPFx4FR5TobYlYHTIWAWkwlIcxKUK4LGGyq42l3 m2/ADIEWVJOqvqG7Ud1DIJpHgli/lGFLwV9XS0etd1iN09/yr1RFTK+cIUE5g5Jk/XoqVMXKyZWm kopIXw0m93aqDYHa248QcDiV3FYvYLSAteKXsfPSUjCuQS1NnH0ncryYUzKM+LncRrTQQw6YHZnM g5duPH5+RqwcVrF1OGsESROc8/MUs39+/Q9fJBm2I1b0luCODmnQRM+8oUFDTB4oWIOA06+VG6M4 iRVwZqR0cBmybACtD3Ct5CcqZIuEeNpoXl/YcQ/9Ms1LRYjtIMJ2+qC6Czuuw8/abCskjeeM9x1F hXzjNHy9pjYRPGOk7Z4HWOB06I/qKngLscDvGGKkeVfbHIxtbUeQhQIynWHTmVEyQj0K5QDhpFsJ b0ajUdd0g8vYcjyENQ4l+/19xGvW8YjlCkBq2QBekPqke4YYLf1DCoIyPQgXEpI9QhWujrdzlWQu N+F/RCxHoeirz/D8TKg8AJh1IOpAvEdxYWFK1DaS7YjpklhwF+rM8bbXDgbvHssuRSxpNJvNymYt K7cowyxJoHPrJcdsdXrmbTHYKkLxa5GHI0nxftAVxh+DfdBDpjuzD8jxrU3IPA4GdAtaKbBs4zoI lJ1ntANRiXSDkZC6PqFUGoKCxBXAJLeZQLOBK1B7GF2DMpALIAoJQGFIJzREijCRHLE6PqnO6JFF 0HtUTLUZBC9N4BkkAN+7HGMoBX5mGzrkI3CE3cidaELxxR0Jpx6VRqhlMBtuZkRKyJqHA52NRDhw FACdg1tTJkpwYDFNcKYk2g8/IdsJJfQnVQLv1RKZC9F8W+qia7mDkcEaEe+2fYcIJZshrEQa5lBg JDqpzd6AysnOfFHz6U50H396BHkMxzS4nFJhP4VZEhYFioINuswEUrkRyLPhXZ7QEjQMx+/jZPGA KYGIVTpWIahmRde2FjqRh3pzhvLQVJC0q+LbWEwFtSWdI6KS7nGMB8KOY8BxF5Byo5m9y8dvaO8Z ZkZcrVaGgYyRmR5wuOW4R8hiR3zGWK1+X2SF0eCzTDHlGp2gk9kAJiaDi9JEyEw1tMgxOpzd0rJA eNXRQIICdlzMmnpklzmQrLX9x0fEXLWjgOlEaOTEdJ5YxdrucMK8xaHe1JgN7i+AidzOIbBG9Gbi lI/6Dj5KZT0xERJ5pchFWByndq3LrCy8NKxwHpysdxux2q3k0R0qoigIPxouQQF9tgCcqA0U3gHk 0GUvDJzM4yXQL52232srRVKCK4KEIcVrodmdUIPQLNIXvIz+QZjxaHXNYPruLMYTYUQI01ukw25o qlMZBwiBZWQgFQOFoCygEosD35+7F66GnbCwrYDPScJJqe83megE1bi7mD6Bj7EaM92JTES3zSyo Llg4n2Kqm6IDmfWaUu45sTmpGj4fGceJNe8Q0jIYRk98Zd/HAvEMwKulyuN3wi9jAp0X4rd4I3n0 eY9RIOMlU8t5BbjqJCOB6iBxNPI2VKAZ8DiAeOB2jB7hrQpTm8NZrCgL1nYZYXE07RniJ1WO0aSs MYBsAJFYZsjkmMwzAG0YVRMvDn4kfYMMcys2EThaPxorVwpEfoLJCZRPyBbt/YMUMBjP8XckU4UJ AnAzpsA= --===============7265386631446262925==--