Hi,

I use dbisam 4.24 ODBC and php.

I have a table (pod) with the following fields:
Member ID
ID
Shipment Number
and some other fields

Indexs On
ID (primary)
Member ID;Shipment Number


when I run the query
SELECT * FROM pod where ([Member ID] ='CDS') and ([ID] ='CDS-200606000058')
it takes 0.4 second.

when I run the query
SELECT * FROM pod where ([Member ID] ='CDS') and ([Shipment Number]
='200509000629')
it takes 9.8 seconds

Why is this second query so slow. I have an index on the fields Member ID
and Shipment Number.
Based on the index you should expected that the first query is slow.

Thanks for your advice.

Andre

Andre

DBISAM only uses first field in the index and therefore you must create
another index starting with the field "Shipment Number".

Eduardo

Andre,

<< Why is this second query so slow. I have an index on the fields Member ID
and Shipment Number. >>

How many rows are being returned by both queries ?

--
Tim Young
Elevate Software
www.elevatesoft.com

Hi Tim,

Both queries always (if it exists) returns 1 record.

andre


"Tim Young [Elevate Software]" <timyoung@elevatesoft.com> schreef in bericht
news:F726CE0F-8F99-48E2-B9B3-123E5D53B8BF@news.elevatesoft.com...
> Andre,
>
> << Why is this second query so slow. I have an index on the fields Member
> ID and Shipment Number. >>
>
> How many rows are being returned by both queries ?
>
> --
> Tim Young
> Elevate Software
> www.elevatesoft.com
>

Hi Eduardo

I did and it works. Thanks.

Andre


"Jose Eduardo Helminsky" <contato@hpro.com.br> schreef in bericht
news:FB548462-C3EF-4E1A-85A2-759371B344DD@news.elevatesoft.com...
> Andre
>
> DBISAM only uses first field in the index and therefore you must create
> another index starting with the field "Shipment Number".
>
> Eduardo
>

Andre,

<< Both queries always (if it exists) returns 1 record. >>

Actually, I asked that question wrong.  What I really needed to know is how
many records would have been returned with each query with just the MemberID
condition.

However, I see that adding the new index solved the problem, so my question
is irrelevant now.

--
Tim Young
Elevate Software
www.elevatesoft.com

Thanks for your reply.
Just for your info. Running the query with only the MemberID set return
about 5000 records.

Indeed the problem was solved by generating a additional index.

Andre

"Tim Young [Elevate Software]" <timyoung@elevatesoft.com> schreef in bericht
news:81B616DD-B120-4868-B8A3-8D78CE6F71ED@news.elevatesoft.com...
> Andre,
>
> << Both queries always (if it exists) returns 1 record. >>
>
> Actually, I asked that question wrong.  What I really needed to know is
> how many records would have been returned with each query with just the
> MemberID condition.
>
> However, I see that adding the new index solved the problem, so my
> question is irrelevant now.
>
> --
> Tim Young
> Elevate Software
> www.elevatesoft.com
>

Andre,

<< Just for your info. Running the query with only the MemberID set return
about 5000 records. >>

That's what I thought.  In that case, adding another index would help since
it would eliminate a 5000 record scan and replace it with a quicker index
scan.  This is especially true if the record size is rather large ( > 500
bytes).

--
Tim Young
Elevate Software
www.elevatesoft.com