>>I currently have about 70,000 records in each table, and adding about 200 each week to both tables (a few more to table2). Peer to peer network, but these queries are always done on the computer with the data on the local drive.
>>Removing the DELETED() filter didn't change the time involved even 1 second either way.
>>I need to find a way to speed the queries up sometime in the next 6 months or so, before it really gets out of hand. I may be over-reacting though. It currently takes about 20 seconds to perform each query. But in a year that will be over a minute each. And hopefully the application will be in use for several years, so it could really get bad. Maybe the client won't mind, but I'd rather not take the chance. I've gotten used to queries taking fractions of a second, and so has my client.
>
>IMO, the time it takes to fetch records with an efficient setup should not vary much, whether you have 10,000 or a million records. Only the amount of records fetched should make a significant difference.
>
>>If I don't find a way to speed them up, I'll have to find a way to filter records that no longer need be included every week. I'd rather not do that, because I'd then have to write a routine that checks them every now & then to make sure nothing has changed.
>
>OK, here are my tests over the network. I created a table with ca. 1 millon records (duplicating, vertically, a smaller table), and wrote a small query that selects 8 records. I ran each test several times, to offset possible effects of buffering, network load, and processing load on my local machine. Also, at the beginning of the test program, I issue CLOSE DATABASE ALL.
>
>No index: 8-13 seconds.
>Index on the field in the WHERE clause: 13 - 31 ms.
>Additional index on deleted(): 100-500 ms. Of course, I had SET DELETED ON.
>
>So in this case, although the difference may not be dramatic between the last two cases, it is definitely better to omit the index on deleted().
>
>Now, let's repeat the test on my local machine.
>
>No index: 7-10 seconds.
>Index on field in WHERE clause: 10-20 ms.
>Additional index on deleted(): ca. 115 ms.
>
>So, here too, the veredict is clearly in favor of deleting the index on deleted().
>
>However, I invite to repeat the tests to more closely match your expected future conditions. For instance, I queried on a single field. You may want to query on multiple fields - although in this case, if you need a particular query frequenty, you can probably make your query more efficient by comparing with a compound key - and having an index on this compound key.
>
>HTH, Hilmar.

Could you please elaborate more? In orher words, say, you have all indexes (except for deleted) and amount of records to retrieve is small. Does it help to have index in place or not?