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.
