>CREATE TABLE TEST(ITEM VARCHAR(20) NOT NULL PRIMARY KEY, VAL BLOB SUB_TYPE
>BINARY);
>SELECT VAL FROM TEST WHERE ITEM = '...';
>
>INSERT INTO TEST(ITEM, VAL) VALUES('...', ...);
>SELECT FIRST 1 RIGHT(ITEM, 8) FROM TEST WHERE LEFT(ITEM, 8) = '...' ORDER BY
>ITEM DESC;
>
>These are sql statements that I will use.
>
>Is it better to create indexes for this table?
>Someone told me that PRIMARY KEY field is automatically indexed internally.
>But I am not sure if it is true or not. Can anyone verify this?
Others have already answered your actual question. I'd like to add a few hints:
1) It is a bad idea to use meaningful data in the primary key. It works OK as
long as you don't change the definition in any way, if there is a slight
possibility of ITEM ever to change from VARCHAR(20) to something else, then the
change would be simpler to implement if you added a meaningless identifier -
typically a 'TEST_ID INTEGER PRIMARY KEY' field being filled through a BEFORE
INSERT trigger. Then have a unique constraint on ITEM (it doesn't really matter
as long as you only have one table, add a few other tables containing fields
that have foreign keys to the TEST table and you'll see how much simpler the
change in the definition of ITEM will be if those foreign keys points to
TEST_ID rather than ITEM).
2) SELECT FIRST 1 RIGHT(ITEM, 8) FROM TEST WHERE LEFT(ITEM, 8) = '...' ORDER BY
ITEM DESC cannot use any index, better change that statement to SELECT FIRST 1
RIGHT(ITEM, 8) FROM TEST WHERE ITEM STARTING '...' ORDER BY ITEM DESC;
3) Maybe (I haven't checked) the STARTING query above could benefit from an
additional DESCending index on ITEM (Firebird indexes are unidirectional and
the unique constraint will automatically generate an ASCending index, I don't
think descending indexes are ever created automatically).
HTH,
Set
