List:General Discussion« Previous MessageNext Message »
From:Zardosht Kasheff Date:March 3 2010 1:58pm
Subject:Re: Re bug#45458
View as plain text  
Hello Jonas,

Thank you for filing this feature request. Are there plans to add
support for clustered indexes in MySQL soon? This is something I have
been researching on and off for a while now. Here are my thoughts.

It seems that there are two parts to this feature request:
1) a new flag that allows the storage engine to report that an index
is clustered
2) changes to the optimizer to properly support clustered keys.

I like #1. The way that I dealt with it was not as good. I added
handler::supports_clustered_keys(), and used that function and
HA_CLUSTERING from my patch to determine if an index is clustered.
Your method is better.

As for #2, I do not think it is enough. Here are two other locations
of code I know that will need to be modified:
1) find_shortest_key in sql/sql_select.cc. (This will be an addition
to MySQL bug #39653)
2) get_best_ror_intersect in sql/opt_range.cc. This is for
index_merge. A patch of what I have done is in the attached file
9-index_merge_clustering.txt. This patch was the result of a long
thread on the internals alias (which you may want to CC for this
discussion). The link to the thread is
http://lists.mysql.com/internals/36977.

There may be more places that need to be modified. I think the
approach to finding out if other places need to be modified is to
pattern match off of how the optimizer deals with clustered v.
non-clustered primary keys. It does so by having a function
handler::primary_key_is_clustered. I think one needs to search the
optimizer for all instances of this function, see why it is being
called, and see if it applies to clustered v. non-clustered secondary
keys as well.

-Zardosht

On Wed, Mar 3, 2010 at 5:57 AM, Jonas Oreland <jonas@stripped> wrote:
> Hi,
>
> I just filed http://bugs.mysql.com/bug.php?id=51687
> which is very related to your bug#45458.
>
> If you would care to look at it and provide feedback,
> I would appreciate it.
>
> /Jonas
>

Index: sql/opt_range.cc
===================================================================
--- sql/opt_range.cc	(revision 17423)
+++ sql/opt_range.cc	(revision 17424)
@@ -4525,6 +4525,7 @@ TRP_ROR_INTERSECT *get_best_ror_intersect(const PA
   ROR_SCAN_INFO *cpk_scan= NULL;
   uint cpk_no;
   bool cpk_scan_used= FALSE;
+  bool supports_clustered_keys = param->table->file->supports_clustered_keys();
 
   if (!(tree->ror_scans= (ROR_SCAN_INFO**)alloc_root(param->mem_root,
                                                      sizeof(ROR_SCAN_INFO*)*
@@ -4536,8 +4537,20 @@ TRP_ROR_INTERSECT *get_best_ror_intersect(const PA
   for (idx= 0, cur_ror_scan= tree->ror_scans; idx < param->keys; idx++)
   {
     ROR_SCAN_INFO *scan;
+    uint keyno= param->real_keynr[idx];
+
     if (!tree->ror_scans_map.is_set(idx))
+    {
       continue;
+    }
+    /*
+      Ignore clustering keys.
+    */
+    if (keyno != cpk_no && param->table->key_info[keyno].flags & HA_CLUSTERING && supports_clustered_keys)
+    {
+      tree->n_ror_scans--;
+      continue;
+    }
     if (!(scan= make_ror_scan(param, idx, tree->keys[idx])))
       return NULL;
     if (param->real_keynr[idx] == cpk_no)
Thread
Re bug#45458Jonas Oreland3 Mar
  • Re: Re bug#45458Zardosht Kasheff3 Mar
    • Re: Re bug#45458Johan De Meersman3 Mar
      • RE: Re bug#45458Gavin Towey3 Mar
        • Re: Re bug#45458Zardosht Kasheff3 Mar