neither of the below were the underlying problem. keys_in_use on the
share, part_of_sortkey for the fields, share->key_info, and
rec_per_key are set up by table.cc:open_binary_frm which is never
called when creating an internal temp table. i put that
initialization into create_tmp_table for the kway merge case and now
it uses the index. :)
any help with my next step:
>>> A. take the value selected with select_idx and use it as a
>>> condition for the next join. instead, it's just using limit/offse
would be appreciated. i've found it tough to figure out how to
construct a condition to pass through mysql_select programatically.
thanks,
eric
On Jul 5, 2009, at 11:56 AM, Eric Jensen wrote:
> digging further into this:
>
> update_ref_and_keys is never called because both conds and
> outer_join are zero in make_join_statistics. can anyone explain how
> keys become "in use" for selects that don't have any conditions and
> only have an order by so that the order by can use the index?
>
> if (conds || outer_join)
> if (update_ref_and_keys(join->thd, keyuse_array, stat, join->tables,
> conds, join->cond_equal,
> ~outer_join, join->select_lex, &sargables))
>
> thanks,
> eric
>
> On Jul 4, 2009, at 3:35 PM, Eric Jensen wrote:
>
>> I believe I have located the reason for B below (the selects on the
>> temporary table failing to use the index). JOIN::exec in
>> sql_select.cc does the following call:
>>
>> if (create_sort_index(thd, curr_join,
>> curr_join->group_list ?
>> curr_join->group_list : curr_join->order,
>> curr_join->select_limit,
>> (select_options & OPTION_FOUND_ROWS ?
>> HA_POS_ERROR : unit->select_limit_cnt),
>> curr_join->group_list ? TRUE : FALSE))
>>
>> However, the definition for that method is:
>>
>> /*
>> ...
>> is_order_by true if we are sorting on ORDER BY, false if
>> GROUP BY
>> Used to decide if we should use index or not
>> ...
>> */
>> create_sort_index(THD *thd, JOIN *join, ORDER *order,
>> ha_rows filesort_limit, ha_rows select_limit,
>> bool is_order_by)
>>
>> Is this a bug that it's passing true for is_order_by when there is
>> a group_list or what don't I understand?
>>
>> Secondary problem: test_if_skip_sort_order would not return true in
>> my case even if I got past this step, since my table-
>> >keys_in_use_for_order_by is zero, as is table-
>> >keys_in_use_for_query, as is table->s->keys_in_use, as is join-
>> >keyuse, as is tab->keyuse, and tab->ref->key = -1, although table-
>> >s->keys is indeed equal to one so it knows there is one on this
>> table, it's just not used for this query. Is there some function
>> that must be called after creating an index on a temporary table to
>> make that index available to selects? It's clearly existing and in
>> use, since it's dedup'ing entries for the unique.
>>
>> I'm having a tough time debugging what is supposed to set all
>> this. Is it get_best_combination setting up join->keyuse which is
>> passed to make_join_statistics which calls update_ref_and_keys or
>> is there somewhere else I need to look to figure out why the key on
>> my temp table isn't set here even though it should cover the order
>> by of this query?
>>
>> Thanks,
>> eric
>>
>> On Jun 5, 2009, at 7:55 PM, Eric Jensen wrote:
>>
>>> it is missing the following functionality for it to be efficient:
>>>
>>> A. take the value selected with select_idx and use it as a
>>> condition for the next join. instead, it's just using limit/offset
>>> B. it doesn't seem to be using the btree index i create on the
>>> temporary table for either the select_idx lookups or the final sort
>>>
>>> anyone's help figuring out / adding any of these things would be
>>> appreciated. the patch is attached.
>>
>
