Indexes

[chiuso2]

hi, this question should appear stupid for an expert..but..
I created an index made by 2 fields F1 and f2 and i called it Index1.F1 is the primary key of the table.
Ok the question is: if i need to search a record stored in this table(with OpenRecordset...) can i use the index Index1
? And if yes, how call it in the OpenRecordset Function??
Another question:search a record stored in a numeric field is faster than search one stored in a txt field?(I'm talking about a table with thousands of record stored in!)
Thanks and greetings from Italy
Mario

 

[Darrylles]

Hi ch,

When you create an index, all you do is tell Access to create a 'lookup' list. If you gave it 2 fields for the lookup, then it creates a list of 2 fields, and creates pointers straight to those records.

When you query on those 2 fields, it checks it's index and goes straight to the record that you require.

(Think of a telephone directory index - one letter - sometimes 2).

To use the index, you do nothing differently, you just create your query ON THE FIELDS THAT YOU SPECIFIED IN THE INDEX.
You decide BEFORE you create indexes, what fields are important.

Q2:

Not strictly true, BUT, strings tend to be longer than numerical fields, AND if you use AUTONUMBER unique identifiers against user entered text fields then yes: the search time will be drastically reduced over thousands of records.

In general: numbers are better - use Autonumber to uniquely identify every table.

Kind regards,

Darrylle

p.s. Fully expect this thread to get very large if my autonumber comments are spotted - lol.




"Never argue with an idiot, he'll bring you down to his level - then beat you with experience." darrylles@totalise.co.uk

 

[chiuso2]

Hi Darrylles, first of all thanks for your response.
I posted my message because i'm getting crazy about this:
I need to search a record throught a list of 300000.This list contains the Codes of the articles we sell in our shop.
I use this method to do it
...
set rst = db.openrecordset("Select from...where

 =" & me!code)
with rst
if rst.recordcount = 0 then 
...
else
me!description = !description(or instead rst.fields("Description")
end if
end with

Ok, it give me a fast response when a set the field "Code" as a numberfield and more slow when i set it as a txt.The real problem is that my articles right now have a code made by 11-12 Characters, but tomorrow? Does exit a kind of NumberField with an illimitaded Number of Characters?
That's why i'm trying to find a fast search method using a txt field
Any suggestion will be wonderful
Thanks in advance
Mario

 

[Darrylles]

Hi,

Have you, in the table design, created an index on this code field?

If not - good. Search for a specific code - time how long it takes.

Now go to table design, click the 'code' field, then at bottom - click 'Indexed - no duplicates'. (I assume ALL codes have to be different).

Exit - do the search again - is it quicker?

Regards,

Darrylle
"Never argue with an idiot, he'll bring you down to his level - then beat you with experience." darrylles@totalise.co.uk

 

[Darrylles]

Hi again,

After thinking, I think I know what you're getting at.

You still need to test what I said above.

If it IS quicker, then yes you can change the field size/type, type shouldn't matter

If it isn't, then we need to look at something else.

Regards,

Darrylle "Never argue with an idiot, he'll bring you down to his level - then beat you with experience." darrylles@totalise.co.uk