CODEWITHSUNDEEP
sqlquery-optimizationfirebirdfirebird2.5
neggenbe
neggenbe

Reputation: 1885

Firebird sql query optimisation

I have a large DB table (>300'000 records) and I want to query fields according to one parameter without joins as follows:

SELECT F1, F2, ..., IS_NOT_TO_LOAD FROM MY_TABLE WHERE IS_NOT_TO_LOAD IS DISTINCT FROM 1;

I have created an index on the table w.r.t. IS_NOT_TO_LOAD field :

CREATE ASC INDEX "IND_MY_TABLE_IS_NOT_TO_LOAD" ON "MY_TABLE" ("IS_NOT_TO_LOAD");

I am not very familiar with execution plans, but I'd expect the query to use the index plan rather than the natural execution plan.

Am I doing something wrong here, or is there a better approach to optimize my query?

UPDATE I found that using the following query:

SELECT F1, F2, ..., IS_NOT_TO_LOAD FROM MY_TABLE WHERE IS_NOT_TO_LOAD < 1;

uses the plan PLAN (A INDEX (IND_MY_TABLE_IS_NOT_TO_LOAD)), but I didn't use this because I sometimes have 0 or null values, sometimes 0

Upvotes: 1

Views: 693

Answers (1)

Daniel Vidić
Daniel Vidić

Reputation: 164

Query can be fast only if WHERE clause can use primary key or index (fields in WHERE must exist in INDEX). You do not have that case, so Firebird must read all records from table to determine which meets WHERE clause. I suggest using index from your question, but first make sure you do not have IS_NOT_TO_LOAD = null (now and in a future).

Upvotes: 1

Related Questions