Hi

i have a huge db with one field string. ( 500'000 records.. )

Example of 4 records :

ABC DEF 1234 MNOP
ABC DEF X1234X MNOP
ABC DEF 1234X MNOP
ABC DEF GHIL MNOP

( i need all 3 record with 1234 )

Can i with EDB 2.x search records but in fast mode using any index.... for
to search the sub-string ?

( with more 3 chars , of example )

There is POSITION FILTER but don't use the indexes ( i think ^^ )

I need use a TEDBTABLE. ( not TEDBQuery )

Best Regards.
Mauro.

Search...   

Mauro


You can try LIKE '%1234%' (remember one of the brilliant bits of DBISAM and ElevateDB is sql type stuff excluding joins can be used in filters) but I don't know how fast it will be. My approach would either to split the field up, create additional tables or to use full text indexing.

If you did the latter you'd need custom text filters to split the data up how you want it but response would be fast.

Roy Lambert

Mauro,

<< Can i with EDB 2.x search records but in fast mode using any index....
for to search the sub-string ? >>

Yes, and I forgot to suggest this instead of POSITION in the other thread.
If you use LIKE with multiple wildcards, ElevateDB will use an available
index (VARCHAR/CHAR only, no CLOB columns) to perform the scan instead of
scanning all of the rows in the table:

MyColumn LIKE '%Something%'

where MyColumn is indexed.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim


>Yes, and I forgot to suggest this instead of POSITION in the other thread.
>If you use LIKE with multiple wildcards, ElevateDB will use an available
>index (VARCHAR/CHAR only, no CLOB columns) to perform the scan instead of
>scanning all of the rows in the table:

I thought I'd seen you post something like that before but couldn't remember for certain so didn't dare suggest it.

Will it actually be any better than scanning all rows in this case? Remember from Mauro's first post "i have a huge db with one field string". So even if indexed unless there's a lot of duplication LIKE will have about the same amount of data to trawl.

Roy Lambert

> Yes, and I forgot to suggest this instead of POSITION in the other thread.
> If you use LIKE with multiple wildcards, ElevateDB will use an available
> index (VARCHAR/CHAR only, no CLOB columns) to perform the scan instead of
> scanning all of the rows in the table:

Which is the difference between  POSITION and LIKE  ?

is there one more optimized ?

( i need filter for sub string - part of word or single word , in indexed
column )

p.s.

i can't to use TEDBQuery, i can to use only TEDBTable.

Mauro


>i can't to use TEDBQuery, i can to use only TEDBTable.

Doesn't matter you can use LIKE in the filter

Roy Lambert [Team Elevate]

Mauro

>Which is the difference between POSITION and LIKE ?
>
>is there one more optimized ?

From Tim's post LIKE will try and use the indices so it should be faster. Is every row in this large table unique or are there any duplicates eg could you have several rows with ABC DEF 1234 MNOP as the field value?

>( i need filter for sub string - part of word or single word , in indexed
>column )

LIKE will do that.

field LIKE 'data%' will give all rows where the field starts with data

field LIKE '%data' will will give all rows where the field ends with data

field LIKE '%data%' will give all rows where the field has data in it

and LIKE can be used in the table filter.

Roy Lambert [Team Elevate]

Roy,

<< Will it actually be any better than scanning all rows in this case?
Remember from Mauro's first post "i have a huge db with one field string".
So even if indexed unless there's a lot of duplication LIKE will have about
the same amount of data to trawl. >>

Yes, it could still be significantly better due to the organization of the
column values on the index pages instead of scattered around (individually)
in the table.

--
Tim Young
Elevate Software
www.elevatesoft.com