setting field searching order to speed up search

[HermanJ]

I'm doing a search in a db with 600.000 records, on multiple fields. This works, but is bit slow, because FM is searching the wrong/difficult fields first. (they are all indexed b.t.w.)

Example:

The db has the fields "place", "street", "number_from", "number_to".

If I do a search on a specific place and street, I'll get about 5 records in a time I can't even blink with my eye's.

If I do a search on the same place and street, but also on the number_from field (example: <7), I first get the messege "find in progress, processing field 'number_from'" (which takes about 10 secs), and then the found set.

I think the search on the "number_from" field takes a while because a lot of records are found with only that constrain, and after that it start searching for the right place and street.

I would like to tell FM to first find the right place and street, and constrain that found set to the "number_from" constrain.

I know for sure that it would be faster, because I kicked out all 599.995 other records from my (copy of) db and did the same, and got the result in a blink of an eye (or even faster)

I already tried the "constrain found set" option, but FM starts a new search all over, so it seems.

I also tried talking to my screen, but it leaves FM unaffected.

Is there a way I can tell FM to search on a specific field first?

FileMaker Version: 6

Platform: Windows XP

[CobaltSky]

Hi HermanJ,

If you want to control the order of searching, you may wish to consider scripting the search process by first collecting the criterua into global fields and then having the script proceed to conduct the search in stages using the 'Constrain found set' option for the second and subsequent searches.

Alternatively, you may find it is faster still to perform the first part of the find, then have the script freeze and loop through the found set omitting records (if any) which don't match the final criterion. Whether or not this will be quicker than a constrain procedure will denpend on the size of the set returned from the initial find, whether or not all the fields you are searching on are indexed and if so, the size of the index and the total number of records.

You might even want to try both approaches and compare them. If you do, you might like to share your findings here...

[HermanJ]

Ray, thanks for your response.

I'm afraid that the first option does not work. In fact, I have tried it already. I've tried it via a script and via the menu option ('constrain found set'). Both with the same disappointing result: slowwwwwwww (and yes, all the fields that are searched have been indexed)

After some experimenting I think FM does the following internally, if you make a search with multiple fields:

1. Search the database for records that fit in the first field constrain.

2. Search the database for records that fit in the second field contrain.

3. Search the database for records that fit in the third field constrain, etc, etc.

4. Combine the records that are found in every of the above searches into one found set.

If this is true (and I hope it isn't), it does not matter if you "constrain a found set", because in fact, FM just searched the whole db again with the new constrain and combines the corresponding found records again.

So, if there isn't anybody who can tell me different, I'm affraid that the second option you suggest, making a script that does the constraining, is the best solution. (but I hate that, because your scripting something that FM already has as a feature)

Well, maybye there is a third option: export the found set to another temp db, constrain this temp db, and somehow export the result back to the original search. But this doesn't sound easy either. Crap!

Herman.

[Ender]

Herman, I have a file with 530,000 records. I performed a test on finds of indexed fields and my results are good. Searching on 4 fields (even with ranges) it found records within a few seconds. Maybe the indexes need to be rebuilt.

FM Server 5.5 on OS 10.3.3, PMG5.

[Vaughan]

It sounds to me like the "number_from" field is not indexed because it is an unstored calculation or a related field. Even if the field is indexed in the related file it's unstored because you're accessing it through a relationship.

[HermanJ]

Hi Ender.

I think that a search of a few seconds is still to long. If the search should be between 2 records!!! The previous search (on other fields) eleminated, though not deleted, the other 599.998 records. Even I can do that faster.

Vaughan, the testing I do is on one file, not using any relation to other files, and all search fields are indexed.

I've tested different search methods. I did a search in one file, no relationsships with others, and all searchable fields indexed. And I found out that:

1. A search on the "number_from" field with a contrain like "<7" takes a while (5 seconds with the 'processing field ...' message in screen) and finds about 300.000 records.

2. A search on the "number_from" field with a constrain like (=)7 takes a split second and finds about 1500 records.

3. A search on the "number_from" field with a constrain like ">300" takes a split second and finds about 1600 records.

4. A search on two fields, like search 1 and search 3 combined (well, different fields, but one would yield 1500 records and the other 300.000 records), takes a lot of time, with again the message 'processing field...'. Even with the "constrain found set" option gives no difference.

5. A search on the "number_from" field with a constrain like "(=)1" takes 0.1 seconds and finds about 180.000 records.

So personally I think that a search with an operator like "<" or ">" takes more time than a search without such a operator (=).

And the more records it finds, the more time it will cost. Especially when you use an operator like "<" and find a lot of records, time needed grows exponentially.

And there is nobody and nothing left to prevent this ....... AAAAARRRRRGGGGG.

Please, let someone say I'm wrong.