Problems with large-sized index / maximun size for index

[giscon]

The SQLBase language reference still documents that the maximum size for an index must be less than 255

Code: Select all

( 6 + number of columns + sum of all columnl length ) <= 255 ) 

But the 255 limit seems to be true only until SQLBase 11.5.
Accidentally we detected that the limit to create an index is much higher now although we had problems with such an index in a productional environment.

It happened that we created an additional index (non unique) on a table in a customer SqlBase 12.0 database for performance reasons. The index contained several columns, and since the index could be created, we overlooked that it was a bit longer than 255.
Primarily this worked well and the performance was significantly better, but on the customer site we had afterwards some database server crashes and each time this new index was corrupt. So we decided not to use anymore this large-sized index.
I can’t give you a reliable testcase which leads to the index corruption, but without that index there were and are no problems and with that large-sized index was always a crash after one or few days


Some simple tries with SqlBase 11.6, 11.7 and 12.0 showed that the formula now must be similar as:

Code: Select all

x +  ( 2 * number of columns ) + ( sum of all column length )

For a simple testcase with only two columns and an index for it, the maximum possible was the following (since nvarchar columns count double):

Code: Select all

create table xindextest ( col1 nvarchar (400) , col2 nvarchar (194) );
create index xie1indextest on xindextest (col1 asc, col2 asc ) ;

or

create table xindextest ( col1 varchar (800) , col2 varchar (389) );
create index xie1indextest on xindextest (col1 asc, col2 asc ) ;

Increasing the length of e.g. col2 only by one would let fail the índex creation with “Index key size too large”. So, the limit is somewhat in the range of 1194 - 1200, depending what x is exactly.

Please tell us the correct actual rule for calculating the index size and its allowed maximum size and update the SQLBase documentation.

And, very important, please check also, whether there are known problems with such large indexes or is there a recommendation not to use such large-sized indexes althought they can be created?

Regards
Günter
Giscon Systems GmbH

[Mike Vandine]

I will need to check with Development about what the maximum is. I'm sure this was changed with the 11.6 version.

We have not had any reports of people having problems with large indexes. Probably means that no one is using indexes that large.

Best regards,

[Mike Vandine]

Hi Günter,

Well, Development have gotten back to me:

Maximum key size is 1200. That’s it.

We try to make sure that three keys will fit on a page. Our big index page is 4096 bytes. Three keys of maximum size of 1200 will consume 3600 bytes. That gives us almost 500 bytes left over for other overhead or more keys.

Also, they have seen some problems with huge indexes. They have asked me to get the exact version of SQLBase that you are using that is giving you errors so we can see if there have been fixes in versions after yours.

Best regards,

[giscon]

Hi Mike,

thanks for the new infos.
The SQLBase we had used with the large index that became several times corrupt was the EDP Version of SQLBase 12.0 32-bit for Windows from August 2015. The original zip file was named sqlbedp-120-10109.zip. With that we installed the EDP-Maker and therewith the installer for the mentioned SQLBase 12.0 DB-Server.

Kind Regards
Günter
Giscon Systems GmbH

[Mike Vandine]

Thanks!

I've passed that info on to the Developers.

Best regards,