Indexes and NULL values

[Ertan Küçükoglu]

Hello,

I am using FirebirdSQL 5.0.2 Win64.

I have a situation where I need to use an SQL like below:

select autoinc,marketplaceordercode,marketplacepackageno
from orders
where marketplacecode=2 and marketplacepackageno is not null and cargotrackingnumber is null

Above   marketplacepackageno and cargotrackingnumber columns are varchar columns.

Since table is due to grow large, I added an index like below

CREATE INDEX IDX_ORDERS3 ON ORDERS (MARKETPLACECODE, MARKETPLACEPACKAGENO, CARGOTRACKINGNUMBER);

When I check the execution plan I see the index is not used. Instead a foreign key which only is based on MARKETPLACECODE column is used.

Select Expression
    -> Filter
        -> Table "ORDERS" Access By ID
            -> Bitmap
                -> Index "FK_ORDERS2" Range Scan (full match)

When I check the index statistics they are like below:

FK_ORDERS2=0.5
IDX_ORDERS3=0.0106382975354791

PK_AUTOINC=0.00934579409658909

Comparing PK_AUTOINC (primary key) and IDX_ORDERS3 and it has a nice selectivity.

I don't know the internals of FirebirdSQL and cannot be sure if there is a way to make it use the index rather than foreign key.

Any help is appreciated.

Thanks & Regards,

Ertan

[liviuslivius]

Hi

You have missed that you query by something like <> not the equality.

If it will be

marketplacepackageno is null it will be used as this is like =

Regards,

Karol Bieniaszewski

-------- Oryginalna wiadomość --------

Od: Ertan Küçükoglu <ertan.k...@gmail.com>

Data: 3.05.2025 22:12 (GMT+01:00)

Do: firebird...@googlegroups.com

Temat: [firebird-support] Indexes and NULL values

--
Support the ongoing development of Firebird! Consider donating to the Firebird Foundation and help ensure its future. Every contribution makes a difference. Learn more and donate here:
https://www.firebirdsql.org/donate
---
You received this message because you are subscribed to the Google Groups "firebird-support" group.
To unsubscribe from this group and stop receiving emails from it, send an email to firebird-suppo...@googlegroups.com.
To view this discussion, visit https://groups.google.com/d/msgid/firebird-support/CAH2i4yc3%3Da5PYp3ufV6rJoC4dCtcGm5u_t2bLbF4dqynM9z7Dw%40mail.gmail.com.

[Ertan Küçükoglu]

Hi,

Yes, I totally missed that non-equality.

Changing the index as below:

create index idx_orders3 on orders(marketplacecode, cargotrackingnumber);

Changing the SQL like below (equality columns first, non-equality column last):

select autoinc,marketplaceordercode,marketplacepackageno
from orders
where marketplacecode=2 and cargotrackingnumber is null and marketplacepackageno is not null

Execution plan now displays the index is now in use

Select Expression
    -> Filter
        -> Table "ORDERS" Access By ID
            -> Bitmap

                -> Index "IDX_ORDERS3" Range Scan (full match)

I believe that selectivity would suffice as I know it reduces the selected number of rows quite a lot.

Thanks & Regards,

Ertan

'liviuslivius' via firebird-support <firebird...@googlegroups.com>, 3 May 2025 Cmt, 23:35 tarihinde şunu yazdı:

To view this discussion, visit https://groups.google.com/d/msgid/firebird-support/4ZqfgS3tZnz20BT%40smtp.poczta.onet.pl.

[Mark Rotteveel]

On 03/05/2025 23:21, Ertan Küçükoglu wrote:
> Yes, I totally missed that non-equality.
>
> Changing the index as below:
> create index idx_orders3 on orders(marketplacecode, cargotrackingnumber);
>
> Changing the SQL like below (equality columns first, non-equality column
> last):
>
> select autoinc,marketplaceordercode,marketplacepackageno
> from orders
> where marketplacecode=2 and cargotrackingnumber is null and
> marketplacepackageno is not null
>
> Execution plan now displays the index is now in use
>
> Select Expression
>     -> Filter
>         -> Table "ORDERS" Access By ID
>             -> Bitmap
>                 -> Index "IDX_ORDERS3" Range Scan (full match)
>
> I believe that selectivity would suffice as I know it reduces the
> selected number of rows quite a lot.

Depending on the exact use case, you could also consider a partial index
(a.k.a. filtered index):

create index idx_orders_partial on order (marketplacecode)
where cargotrackingnumber is null and marketplacepackageno is not null

Or

create index idx_orders_partial on order (marketplacecode,
cargotrackingnumber)
where marketplacepackageno is not null

Or some other combination.
--
Mark Rotteveel

[Ertan Küçükoglu]

'Mark Rotteveel' via firebird-support <firebird...@googlegroups.com>, 4 May 2025 Paz, 12:14 tarihinde şunu yazdı:

Depending on the exact use case, you could also consider a partial index
(a.k.a. filtered index):

create index idx_orders_partial on order (marketplacecode)
where cargotrackingnumber is null and marketplacepackageno is not null

 

I also forgot about partial indexes. This example above is the best in my use case and is the fastest in my tests.

Now I need to review several SQLs and see if they are a good candidate for partial indexes.

Thank you.