On the question why Maria_Toku_Optimizer_Off is so much slower than Maria_Myisam_Optimizer_off. Comparing the query plans:

diff -u Maria_MyIsam_Optimizer_Off/Extended\ Plan.csv Maria_Toku_Optimizer_Off/Extended\ Plan.csv 
--- Maria_MyIsam_Optimizer_Off/Extended Plan.csv        2014-05-22 20:51:52.000000000 +0400
+++ Maria_Toku_Optimizer_Off/Extended Plan.csv  2014-05-22 20:59:34.000000000 +0400
@@ -1,5 +1,5 @@
 id,select_type,table,type,possible_keys,key,key_len,ref,rows,filtered,Extra
-1,PRIMARY,wh_fbk_data_ctxt_d,ref,"PRIMARY,WH_FBK_DATA_CTXT_D_N1,WH_FBK_DATA_CTXT_D_N2,WH_FBK_DATA_CTXT_D_M1,WH_FBK_DATA_CTXT_D_M2",PRIMARY,8,const,14,100.00,"Using where; Using temporary; Using filesort"
+1,PRIMARY,wh_fbk_data_ctxt_d,ref,"PRIMARY,WH_FBK_DATA_CTXT_D_N1,WH_FBK_DATA_CTXT_D_N2,WH_FBK_DATA_CTXT_D_M1,WH_FBK_DATA_CTXT_D_M2",PRIMARY,8,const,15,100.00,"Using where; Using temporary; Using filesort"

 1,PRIMARY,wh_tag_group_d,ref,WH_TAG_GROUP_D_M1,WH_TAG_GROUP_D_M1,8,const,4,100.00,"Using where"

-1,PRIMARY,wh_tag_d,ref,"PRIMARY,WH_TAG_D_M2,WH_TAG_D_M1",WH_TAG_D_M1,8,const,50,100.00,"Using where; Distinct"
+1,PRIMARY,wh_tag_d,ref,"PRIMARY,WH_TAG_D_M2,WH_TAG_D_M1",PRIMARY,8,const,75,100.00,"Using where; Distinct"

-2,MATERIALIZED,wh_tagged_attr_fbk_data_f,ALL,"PRIMARY,WH_TAGGED_ATTR_FBK_DATA_F_M1,WH_TAGGED_ATTR_FBK_DATA_F_F3,WH_TAGGED_ATTR_FBK_DATA_F_F1,WH_TAGGED_ATTR_FBK_DATA_F_F2,WH_TAGGED_ATTR_FBK_DATA_F_F4",NULL,NULL,NULL,842779,100.00,"Using where"
+2,MATERIALIZED,wh_tagged_attr_fbk_data_f,ref,"PRIMARY,WH_TAGGED_ATTR_FBK_DATA_F_M1,WH_TAGGED_ATTR_FBK_DATA_F_F3,WH_TAGGED_ATTR_FBK_DATA_F_F1,WH_TAGGED_ATTR_FBK_DATA_F_F2,WH_TAGGED_ATTR_FBK_DATA_F_F4",WH_TAGGED_ATTR_FBK_DATA_F_F3,8,const,8427,100.00,"Using where"

The join orders are the same.

• wh_fbk_data_ctxt_d has small difference in #rows.
• Access to wh_tag_d uses different indexes (WH_TAG_D_M1 vs PRIMARY) and different #rows (50 vs 75). This shouldn't cause 10x difference...
• wh_tagged_attr_fbk_data_f is vastly different. MyISAM uses full table scan, expecting 842,779 rows. With TokuDB, the query plan is to use ref(const) on index WH_TAGGED_ATTR_FBK_DATA_F_F3, and it expects to read 8427 rows.

I suspect TokuDB returns a very optimistic records_in_range estimate for wh_tagged_attr_fbk_data_f. If one really could read just 8427 rows from that table, we would have picked ref access with MyISAM.

Sivaram Dandibhotla, can you try the query with "FROM wh_tag_d" replaced with "FROM wh_tag_d USE INDEX()". TokuDB, optimizer_switch='semijoin=off' ?

Replacing "wh_tag_d" with "wh_tag_d USE INDEX()" didn't make any difference, it still takes approximately 10 seconds.

Thanks for the answer. I am sorry, I have made a typo - we need "USE INDEX() not for wh_tag_d, but for wh_tagged_attr_fbk_data_f (as this is the table for which the query plan is different).

Could you please try the query with "FROM wh_tagged_attr_fbk_data_f" replaced with "FROM wh_tagged_attr_fbk_data_f USE INDEX()". TokuDB, optimizer_switch='semijoin=off' ?

Now the response comes back in less than second as compared to approximately 9 seconds without this hint. Looks like with this hint optimizer is doing a full table scan but still faster even though the index that is used earlier (without this hint) is the right index to be used for the join condition. Can you please explain this behavior.

Earlier we noticed that when we use "index" hints optimizer behavior is unpredictable, performance varies for different data sets of same size. Hence want to fully understand the risk involved in using this hint.

Thanks

Siva

Looks like with this hint optimizer is doing a full table scan but still faster even though the index that is used earlier (without this hint) is the right index to be used for the join condition.

If using the index is slower, then it's not the right index to be used

It is not always faster to use an index. Using an index is faster only if you can read fewer records. If you read nearly the same number of records, scanning through an index is typically slower than doing a full table scan.

Let's look at the last line of the diff posted above:

-2,MATERIALIZED,wh_tagged_attr_fbk_data_f,ALL,"PRIMARY,WH_TAGGED_ATTR_FBK_DATA_F_M1,WH_TAGGED_ATTR_FBK_DATA_F_F3,WH_TAGGED_ATTR_FBK_DATA_F_F1,WH_TAGGED_ATTR_FBK_DATA_F_F2,WH_TAGGED_ATTR_FBK_DATA_F_F4",NULL,NULL,NULL,842779,100.00,"Using where"
+2,MATERIALIZED,wh_tagged_attr_fbk_data_f,ref,"PRIMARY,WH_TAGGED_ATTR_FBK_DATA_F_M1,WH_TAGGED_ATTR_FBK_DATA_F_F3,WH_TAGGED_ATTR_FBK_DATA_F_F1,WH_TAGGED_ATTR_FBK_DATA_F_F2,WH_TAGGED_ATTR_FBK_DATA_F_F4",WH_TAGGED_ATTR_FBK_DATA_F_F3,8,const,8427,100.00,"Using where"

With MyISAM, the optimizer decided to do a full scan and scan 842779 rows.
With TokuDB, the optimizer decided that using index WH_TAGGED_ATTR_FBK_DATA_F_F3 it will only need to scan 8427 rows. However, when I look at Maria_Toku_Optimizer_On/Status-after-query-execution.csv attached to MDEV-6239, I see

...
Handler_read_key,51
Handler_read_next,842815
...

which shows that query execution has read 842K rows in total. All other tables in the query are smaller than that, so I conclude that these 842K rows were read from wh_tagged_attr_fbk_data_f. That is, TokuDB has grossly underestimated the number of rows it will have to read through index WH_TAGGED_ATTR_FBK_DATA_F_F3.

This needs attention of a TokuDB developer.

Rich Prohaska, here we have a case where the only explanation of what's going on is that ha_toku::records_in_range() returns a value that is 100x smaller than the reality. Is it a known problem with TokuDB? Are there any workarounds? (e.g. will ANALYZE TABLE help?)

Will this fix be available in 10.0.13? Please confirm.

Since the problem seems to be in TokuDB, we depend on Tokutek for the fix. I'll ping them about this.

Ok, TokuDB team will start looking into this next week. Since we don't yet know any details about the problem, it is not yet possible to tell whether 10.0.13 will have a fix for this.