Icon View Thread

The following is the text of the current message along with any replies.
Messages 1 to 6 of 6 total
Thread Select DISTINCT don't use any index
Tue, Sep 12 2006 11:17 AMPermanent Link

"Enrico Ghezzi"
Hi

I have this SQL

SELECT DISTINCT FIELDNAME1 FROM MYDB

..dat  is 100 MB , 80'000 records.
record result should to be only 100 records.

but

this query is very slow, i have add any index .. but.. notting.

Are there any plans to implement DISTINCT processing that uses an index when
available ?



dbisam 4.2x ,  Delphi 7

Tue, Sep 12 2006 12:36 PMPermanent Link

"Johnnie Norsworthy"
"Enrico Ghezzi" <EnricoGhezzi@Ghezzi.com> wrote in message
news:7B4AA14F-3C79-48AC-9432-6C3A65F561E0@news.elevatesoft.com...

> SELECT DISTINCT FIELDNAME1 FROM MYDB
>
> .dat  is 100 MB , 80'000 records.
> record result should to be only 100 records.
> this query is very slow, i have add any index .. but.. notting.

Enrico,

I'm sure Tim will answer your question concerning DBISAM directly, but I
thought you might want to take a look at this and might give it a try:
http://www.grebarsys.com/html/superfastdistinct_.html

-Johnnie

Tue, Sep 12 2006 4:15 PMPermanent Link

Tim Young [Elevate Software]

Elevate Software, Inc.

Avatar

Email timyoung@elevatesoft.com

Enrico,

<< .dat  is 100 MB , 80'000 records.
record result should to be only 100 records.

but

this query is very slow, i have add any index .. but.. notting.

Are there any plans to implement DISTINCT processing that uses an index
when available ? >>

Yes, but in the mean time just leave off the DISTINCT if you know that there
is a unique or primary index on the selected columns.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tue, Sep 12 2006 4:32 PMPermanent Link

"Robert"

"Tim Young [Elevate Software]" <timyoung@elevatesoft.com> wrote in message
news:4C6ADD80-7B20-4A8B-9BFD-CCF2720719BF@news.elevatesoft.com...
>
> Yes, but in the mean time just leave off the DISTINCT if you know that
> there is a unique or primary index on the selected columns.
>

Not an option, IMO. If the table contains 80K records and he is only
expecting 100 as a result of DISTINCT, then obviously the field is not
unique.

However,

SELECT FIELD FROM TABLE GROUP BY FIELD

might give you the correct set in a reasonable time.

Robert

Fri, Sep 15 2006 10:13 AMPermanent Link

"Enrico Ghezzi"

> However,
>
> SELECT FIELD FROM TABLE GROUP BY FIELD
>
> might give you the correct set in a reasonable time.


Hi

i have try it.

work well Wink


but it dont' use any index ( always low , like select distinct )

Fri, Sep 15 2006 11:57 AMPermanent Link

"Robert"

"Enrico Ghezzi" <EnricoGhezzi@Ghezzi.com> wrote in message
news:373C4B1A-A4CD-4020-8BBD-97133B936895@news.elevatesoft.com...
>
>> However,
>>
>> SELECT FIELD FROM TABLE GROUP BY FIELD
>>
>> might give you the correct set in a reasonable time.
>
>
> Hi
>
> i have try it.
>
> work well Wink
>
>
> but it dont' use any index ( always low , like select distinct )
>

True, as Tim explained, DBISAM simply will not use the index for these
selects. I thought it might be faster with group by.

If you need speed, you'll have to code a loop using a ttable. Let's say your
table is indexed by FIELD and ID

var prev : string;

first;
prev := field;
while not eof
 store prev;
 FindNearest[prev, 99999]; << should position you in the next FIELD
end

Never done anything like this, but seems to me it should work.

Robert

Image