Slow Searches / Lookups

May be asking a little too much here, but after some tips if I may? I am not after answers and happy to learn and sling about ideas.

2,000 records. They are cities, towns and a little data associated with them. To populate a dropdown box with the unique cities can take a minute. Using a second drop down box for suburb that filters based on the selection of the first combo box takes longer. Even if I allow duplicates the time taken is not useable.

The lookup I am using first has no constraints on it. It basically is looking for all of the cities and putting the unique ones into the dropdown.

I notice too even when using @AliFarahat 's absolutely awesome search tool, the delay is huge. So it is my belief that there may be a full table scan going on. With a normal database, the lookup would be achieved via a fast index scan.

It is also noticeable that the second search on a key takes about half as long, probably due to caching.

Any thoughts on what I can achieve to speed this up a little? In context it is an object of about 2,500 schools and I am returning unique cities.

Thanks so much!!!

Replying to my own post - have just noticed Josh’s post. May try it toorrow.

_we don’t have an index yet, so that will be noticeably slower (perhaps very slow) on big databases, but we auto-detect that happening and build an index, so that in maybe 15 minutes to an hour, that search is fast. So, I wouldn’t worry too much about cleaning up old data. That said, if you know you never need a piece of data again, it doesn’t hurt to delete it.

May try again in the morning…

I think with alifarats plugin it’s done client side which I believe is slower
I would be making sure you use search and not filter.

Good point. Data transfer probably makes up a significant portion. Working thru different options to see what is snappiest.