Base search on primary key of table is slow

[RichA1]

I am using Base to connect to a remote MYSQL database. One of the tables has about 4500 records.

I created a Base form to access this table. The form initially displays with the first 10 or so records. I performed a Search for the primary key of about the 500th record in the table and it took forever. I could see record numbers slowly incrementing–it seemed like it was retrieving one record at a time and checking the key field for the search value.

I then created an index on the table, to see if that would speed up the process. I did this directly on the MYSQL database itself, not going through Base. After that, Base still seemed to be looking record-by-record, perhaps ignoring the index and perhaps not knowing about the incex.

Is there any way to speed this up? Is there a way to get Base to use the index on the table when it searches?

I have some familiarity with MYSQL and Postgresql, but I am new to Base. I am trying to use it to create a front-end for some non-technical people who don’t know SQL and just need to enter and access data.

Thanks very much.

[LiboV]

Your right - the search function will always go record by record, and it will always retrieve the data into the actual data control you are searching (though in the background) - this is because search offers the ability to search for items as presented to the end user and offers the ability to search across multiple data fields.

As you found - filtering on the other hand actually generates a select statement in the background and issues that to the database, and is much faster then.

Be aware though that there is one other difference, the result set you are working with does not change with a search but does with a filter. A search changes the record cursor within the current result set, a filter generates a new and different result set.

[RichA1]

It looks like Form-based filters is what I need to use.