To the best of my knowledge VARCHAR data type columns are not allowed in
INDEXES. Even if they are allowed index length restrictions would apply. See
following quote included in this email. Instead consider adding computed
column(s) of type INTEGER "(IFNULL(sympt1,1,0))" and/or "(IFNULL(Subjx,1,0))"
or
perhaps combine the two tests into one column.
A basic question I have is whether the "Subjx" column really needs to be
VARCHAR. Could a TEXT or NOTE column could be used. Remember both the TEXT
and NOTE column can be set up with a maximum size. One of the most overlooked
syntax options is to define a column as "colname NOTE (300)" where in my
example
you have limited the note columns from 1 to 300 characters. TEXT columns can be
up to 1500 characters and NOTE columns can be a maximum of 4092 characters. Of
course those data type size are limited by the 32 bit version limit of 4096 and
whatever the 64 bit row limit is.
The following is information on text size limits in indexes
"
Full- and Partial-Text Indexes
Text columns can make effective indexed columns. If the size of the column that
has a TEXT data type is 200 bytes or less, R:BASE creates a full-text index. A
full-text index is an index that stores the entire contents of a column as an
index in File 3, which is the file that contains indexes to columns. If the
size of the column is greater than 200 bytes, R:BASE creates a partial-text
index.
If you specify the SIZE option to be less than the defined length of a column,
R:BASE creates a partial-text index, and any text column that has a defined
length over 200 bytes must be a partial-text index. For columns that have a
TEXT data type and exceed 200 bytes, you can specify the SIZE option to be
between 0 and 196 to create a partial-text index. Specifying the size allows
you to base your index on a specified number of characters at the beginning of
the columns and to hash the remaining characters. For example, you can index a
225-character column with a TEXT data type by specifying the SIZE option to be
any number less than 197 bytes. R:BASE will create an index with the first n
characters and the rest of the value will be stored as a four-byte hashed
representation of the text.
Partial-text indexes minimize storage space. However, partial-text indexes
might
not be as efficient as a full-text index, for example:
CREATE TABLE cities (cityname TEXT(40), state TEXT(2), country +
TEXT(20))
CREATE INDEX cityindex ON cities (cityname, state)
INSERT INTO cities VALUES('Bellevue','WA','USA')
INSERT INTO cities VALUES('Belltown','PA','USA')
SELECT cityname, state from cities WHERE cityname = 'Bellevue'
In the above example, because the query reads data only from the index named
cityindex, there is no need to read the actual data stored in File 2-which is
the data file-so the query is done quickly. The query is an index-only
retrieval and produces fast results.
If a partial-text index was used in the same query as above, the partial-text
index could also only use the index named cityindex. Because the partial-text
index only preserves the first four characters, it is impossible to return the
correct answer to the query from the index. The query, as shown below, would
slow processing because R:BASE must read data from the R:BASE data file.
CREATE INDEX cityindex ON cities (cityname SIZE 4, state)
SELECT cityname, state from cities WHERE cityname = 'Bellevue'
When creating text indexes, be aware of the following:
· If you omit the SIZE option and the text field in the column is greater than
200 bytes, R:BASE creates a partial text index by storing the first 32 bytes
of
each field and hashing the remaining bytes in each field into a four-byte
numeric representation of the text. For example, if the text is 280 bytes and
you do not specify a size, R:BASE stores the first 32 bytes of each field and
hashes the remaining 248 bytes into a four-byte integer.
· If you specify the SIZE option to be 16 bytes for a 60-byte column with a
TEXT data type, R:BASE stores the first 16 bytes of each 60-byte text field and
hashes the remaining bytes in each field into a four-byte numeric
representation
of the text. The total length of each index entry will be 20 bytes (16 + 4).
· If you specify the SIZE option to be 30-bytes for a 250-byte column with a
TEXT data type, R:BASE stores the first 30 bytes of each 250-byte field and
hashes the remaining bytes in each field into a four-byte numeric
representation
of the text. The total length of each index entry will be 34 bytes.
· If you specify the SIZE option to be 250 bytes for a column with a TEXT data
type, you have made an illegal request because the maximum value for the SIZE
option is 196 bytes when the length of the text field is greater than 200
bytes.
If you specified the SIZE option to be 196 bytes for a 250-byte column, R:BASE
would hash the remaining 54 bytes into a four-byte numeric representation of
the
text.
· If you omit the SIZE option and the text field in the column is 200 bytes or
less, R:BASE creates a full-text index. For example, if the text is 80 bytes
and
you do not specify a size, R:BASE builds a full-text index of 80 bytes.
"
Jim Bentley
American Celiac Society
[email protected]
tel: 1-504-737-3293
----- Original Message ----
From: Dennis McGrath <[email protected]>
To: RBASE-L Mailing List <[email protected]>
Sent: Wed, July 14, 2010 4:14:05 PM
Subject: [RBASE-L] - Re: How to make this Delete command faster
If you can't from the R> and you can from the designer, I think it is time for
a
bug report. I have on occasion found the designer would do things that were
not
technically allowed.
I do all changes on my live db in code.
As you see, this methodology saves you from headaches later.
Dennis McGrath
-----Original Message-----
From: [email protected] [mailto:[email protected]] On Behalf Of MDRD
Sent: Wednesday, July 14, 2010 4:06 PM
To: RBASE-L Mailing List
Subject: [RBASE-L] - Re: How to make this Delete command faster
but, I made the Index on all 3 columns using the Design mode?
Then Unloaded the structure, cut and pasted the Index code.
So maybe you can't Index varchars form the R>?
I think it is due to sun spots and solar activity!
Marc
--------------------------------------------------
From: "Albert Berry" <[email protected]>
Sent: Wednesday, July 14, 2010 3:43 PM
To: "RBASE-L Mailing List" <[email protected]>
Subject: [RBASE-L] - Re: How to make this Delete command faster
> That's entirely possible. I did not notice that the column was varchar.
> Albert
>
> MDRD wrote:
>> Albert
>>
>> CREATE INDEX SxTC ON `TravCard` ( `Subjx` ASC )
>> error
>> this works
>> CREATE INDEX SxTC ON `TravCard` ( `sympt1` ASC )
>>
>> Subjx is a Varchar, Is Indexing Varchar is allowed??
>> This is my first Varchar column.
>> Marc
>>
>
>
