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.
>
