Re bug#45458

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

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Re bug#45458

--0015174c14fc4111190480e5e0ad
Content-Type: text/plain; charset=ISO-8859-1

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

--0015174c14fc4111190480e5e0ad
Content-Type: text/plain; charset=US-ASCII; name="9-index_merge_clustering.txt"
Content-Disposition: attachment; filename="9-index_merge_clustering.txt"
Content-Transfer-Encoding: base64
X-Attachment-Id: f_g6c6pzs20

SW5kZXg6IHNxbC9vcHRfcmFuZ2UuY2MKPT09PT09PT09PT09PT09PT09PT09 PT09PT09PT09PT09
PT09PT09PT09PT09PT09PT09PT09PT09PT09PT09PT09PQotLS0gc3FsL29w dF9yYW5nZS5jYwko
cmV2aXNpb24gMTc0MjMpCisrKyBzcWwvb3B0X3JhbmdlLmNjCShyZXZpc2lv biAxNzQyNCkKQEAg
LTQ1MjUsNiArNDUyNSw3IEBAIFRSUF9ST1JfSU5URVJTRUNUICpnZXRfYmVz dF9yb3JfaW50ZXJz
ZWN0KGNvbnN0IFBBCiAgIFJPUl9TQ0FOX0lORk8gKmNwa19zY2FuPSBOVUxM OwogICB1aW50IGNw
a19ubzsKICAgYm9vbCBjcGtfc2Nhbl91c2VkPSBGQUxTRTsKKyAgYm9vbCBz dXBwb3J0c19jbHVz
dGVyZWRfa2V5cyA9IHBhcmFtLT50YWJsZS0+ZmlsZS0+c3VwcG9ydHNfY2x1 c3RlcmVkX2tleXMo
KTsKIAogICBpZiAoISh0cmVlLT5yb3Jfc2NhbnM9IChST1JfU0NBTl9JTkZP KiopYWxsb2Nfcm9v
dChwYXJhbS0+bWVtX3Jvb3QsCiAgICAgICAgICAgICAgICAgICAgICAgICAg ICAgICAgICAgICAg
ICAgICAgICAgICAgICAgIHNpemVvZihST1JfU0NBTl9JTkZPKikqCkBAIC00 NTM2LDggKzQ1Mzcs
MjAgQEAgVFJQX1JPUl9JTlRFUlNFQ1QgKmdldF9iZXN0X3Jvcl9pbnRlcnNl Y3QoY29uc3QgUEEK
ICAgZm9yIChpZHg9IDAsIGN1cl9yb3Jfc2Nhbj0gdHJlZS0+cm9yX3NjYW5z OyBpZHggPCBwYXJh
bS0+a2V5czsgaWR4KyspCiAgIHsKICAgICBST1JfU0NBTl9JTkZPICpzY2Fu OworICAgIHVpbnQg
a2V5bm89IHBhcmFtLT5yZWFsX2tleW5yW2lkeF07CisKICAgICBpZiAoIXRy ZWUtPnJvcl9zY2Fu
c19tYXAuaXNfc2V0KGlkeCkpCisgICAgewogICAgICAgY29udGludWU7Cisg ICAgfQorICAgIC8q
CisgICAgICBJZ25vcmUgY2x1c3RlcmluZyBrZXlzLgorICAgICovCisgICAg aWYgKGtleW5vICE9
IGNwa19ubyAmJiBwYXJhbS0+dGFibGUtPmtleV9pbmZvW2tleW5vXS5mbGFn cyAmIEhBX0NMVVNU
RVJJTkcgJiYgc3VwcG9ydHNfY2x1c3RlcmVkX2tleXMpCisgICAgeworICAg ICAgdHJlZS0+bl9y
b3Jfc2NhbnMtLTsKKyAgICAgIGNvbnRpbnVlOworICAgIH0KICAgICBpZiAo IShzY2FuPSBtYWtl
X3Jvcl9zY2FuKHBhcmFtLCBpZHgsIHRyZWUtPmtleXNbaWR4XSkpKQogICAg ICAgcmV0dXJuIE5V
TEw7CiAgICAgaWYgKHBhcmFtLT5yZWFsX2tleW5yW2lkeF0gPT0gY3BrX25v KQo=

--0015174c14fc4111190480e5e0ad
Content-Type: text/plain; charset=us-ascii


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org
--0015174c14fc4111190480e5e0ad--

Re: Re bug#45458

--0014853925b2a1173d0480e61d3d
Content-Type: text/plain; charset=ISO-8859-1

Unless I'm very much mistaken, InnoDB tables always have a clustered index
as their primary key.

On Wed, Mar 3, 2010 at 2:58 PM, Zardosht Kasheff wrote:

> 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 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
> >
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=vegivamp@tuxera.be
>



--
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

--0014853925b2a1173d0480e61d3d--

RE: Re bug#45458

Yes, but the optimizer doesn't know that.

-----Original Message-----
From: vegivamp@gmail.com [mailto:vegivamp@gmail.com] On Behalf Of Johan De =
Meersman
Sent: Wednesday, March 03, 2010 6:16 AM
To: Zardosht Kasheff
Cc: Jonas Oreland; mysql@lists.mysql.com
Subject: Re: Re bug#45458

Unless I'm very much mistaken, InnoDB tables always have a clustered index
as their primary key.

On Wed, Mar 3, 2010 at 2:58 PM, Zardosht Kasheff wrote=
:

> 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 wrote:
> > Hi,
> >
> > I just filed http://bugs.mysql.com/bug.php?id=3D51687
> > which is very related to your bug#45458.
> >
> > If you would care to look at it and provide feedback,
> > I would appreciate it.
> >
> > /Jonas
> >
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dvegivamp@tuxera.b=
e
>



--
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

This message contains confidential information and is intended only for the=
individual named. If you are not the named addressee, you are notified th=
at reviewing, disseminating, disclosing, copying or distributing this e-mai=
l is strictly prohibited. Please notify the sender immediately by e-mail i=
f you have received this e-mail by mistake and delete this e-mail from your=
system. E-mail transmission cannot be guaranteed to be secure or error-fre=
e as information could be intercepted, corrupted, lost, destroyed, arrive l=
ate or incomplete, or contain viruses. The sender therefore does not accept=
liability for any loss or damage caused by viruses or errors or omissions =
in the contents of this message, which arise as a result of e-mail transmis=
sion. [FriendFinder Networks, Inc., 220 Humbolt court, Sunnyvale, CA 94089,=
USA, FriendFinder.com

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

Re: Re bug#45458

I believe the optimizer does know that InnoDB has a clustered primary
key, because ha_innobase::primary_key_is_clustered returns true

On Wed, Mar 3, 2010 at 2:22 PM, Gavin Towey wrote:
> Yes, but the optimizer doesn't know that.
>
> -----Original Message-----
> From: vegivamp@gmail.com [mailto:vegivamp@gmail.com] On Behalf Of Johan D=
e Meersman
> Sent: Wednesday, March 03, 2010 6:16 AM
> To: Zardosht Kasheff
> Cc: Jonas Oreland; mysql@lists.mysql.com
> Subject: Re: Re bug#45458
>
> Unless I'm very much mistaken, InnoDB tables always have a clustered inde=
x
> as their primary key.
>
> On Wed, Mar 3, 2010 at 2:58 PM, Zardosht Kasheff wro=
te:
>
>> 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 wrote:
>> > Hi,
>> >
>> > I just filed http://bugs.mysql.com/bug.php?id=3D51687
>> > which is very related to your bug#45458.
>> >
>> > If you would care to look at it and provide feedback,
>> > I would appreciate it.
>> >
>> > /Jonas
>> >
>>
>>
>> --
>> MySQL General Mailing List
>> For list archives: http://lists.mysql.com/mysql
>> To unsubscribe: =A0 =A0http://lists.mysql.com/mysql?unsub=3Dvegivamp@tux=
era.be
>>
>
>
>
> --
> Bier met grenadyn
> Is als mosterd by den wyn
> Sy die't drinkt, is eene kwezel
> Hy die't drinkt, is ras een ezel
>
> This message contains confidential information and is intended only for t=
he individual named. =A0If you are not the named addressee, you are notifie=
d that reviewing, disseminating, disclosing, copying or distributing this e=
-mail is strictly prohibited. =A0Please notify the sender immediately by e-=
mail if you have received this e-mail by mistake and delete this e-mail fro=
m your system. E-mail transmission cannot be guaranteed to be secure or err=
or-free as information could be intercepted, corrupted, lost, destroyed, ar=
rive late or incomplete, or contain viruses. The sender therefore does not =
accept liability for any loss or damage caused by viruses or errors or omis=
sions in the contents of this message, which arise as a result of e-mail tr=
ansmission. [FriendFinder Networks, Inc., 220 Humbolt court, Sunnyvale, CA =
94089, USA, FriendFinder.com
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: =A0 =A0http://lists.mysql.com/mysql?unsub=3Dzardosht@gmai=
l.com
>
>

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg