I have situation as follows: I'm having a table
OFERTY of 10k+ records, and when I
select * from it, I get
PLAN (OFERTY NATURAL) Executing... Done. 656 fetches, 0 marks, 0 reads, 0 writes. 0 inserts, 0 updates, 0 deletes, 0 index, 312 seq. Delta memory: -95312 bytes. Total execution time: 0.188s Script execution finished.
This is all fine, but a problem manifests when I need to present data from many joined tables on a listview, and allow filtering on text fields using
starting with clause. I get these numbers:
PLAN JOIN (JOIN (OFERTY NATURAL, KL INDEX (KONTRAHENT_PK), PL INDEX (KONTRAHENT_PK), PERSONEL INDEX (RDB$PRIMARY28), STATUSY INDEX (RDB$PRIMARY34), KOLORYSTYKA INDEX (KOLORYSTYKA_PK), MODELE INDEX (MODELE_PK), A INDEX (IDX_SLOWNIKI_NUM_DZ), B INDEX (IDX_SLOWNIKI_NUM_DZ), C INDEX (IDX_SLOWNIKI_NUM_DZ), RKOL INDEX (REJESTR_PK), RSZY INDEX (REJESTR_PK)), KLIENCI_PROJEKTANCI INDEX (RDB$PRIMARY53)) Executing... Done. 67370 fetches, 0 marks, 0 reads, 0 writes. 0 inserts, 0 updates, 0 deletes, 11199 index, 10669 seq. Delta memory: -20 bytes. Total execution time: 2.610s Script execution finished.
As you can see, query time is skyrocketing. I think it reads ALL the records instead of first-some and I don't know why. There are indexes everywhere there's a need for them (as far as I know). The very same query, but without following text search clause:
/*kl.kt_nazwa is from table KONTRAHENCI which has alias KL and is joined to OFERTY*/ and kl.kt_nazwa starting with 'strefa'
has exactly the same plan, but performs as follows:
Executing... Done. 10801 fetches, 0 marks, 0 reads, 0 writes. 0 inserts, 0 updates, 0 deletes, 2444 index, 200 seq. Delta memory: -1036 bytes. Total execution time: 0.203s Script execution finished.
That's a HUGE difference. It doesn't need to read ALL the records from table
KONTRAHENCI. So I have two questions here:
- Is there a way of text search in firebird that does not mandate reading ALL records from table (in this case:
- there's also awful lot of fetches. Please explain what they are?