How to speed up make-tables/select queries?

[Access9001]

Whenever I have make-table queries or select queries on larger sets of data (say, 900k+ records), it takes forever. Is there ANYTHING I can do to speed things up?

 

[Galaxiom]

Index any field that is used in a join or where clause of the query.

 

[Access9001]

Would I put these indexes in the table itself? Could I have multiple indexes? Could I just index every field? What would an index look like in the table/query?

 

[Galaxiom]

The indexes are set up automatically behind the scenes. You just need to adjust the index property of the field in the backend table.

Indexed: Yes (Allow Duplicates)

You can have multiple indexes but don't index every field as this will slow down changes in the table unnecessarily.

 

[Access9001]

How do I know which fields to index if a variety of queries use many different fields to join on (like some queries use table field A to join on, others use B, others use C, etc)?

 

[Galaxiom]

Just index any fields used in joins or Where clauses.

Another factor to be aware of is the target of the Where clause in the select. Not always possible but whereever you are able, apply the clause to the small table of the join.

Also note that queries are much slower if you are joining or applying the Where clause on a text field, particularly if the value has several characters. If there are multiplerecords that repeat a text value, the values should be recorded as an integer datatype. A lookup table is used to convert the integer to the text for display.