In my Access runtime applications I often include a ‘locator field’ search box to speed-up searches on tabular forms.
The user can choose (via radio buttons) which column field to search by and as each character is entered into a search box the record that matches the pattern will become the active record.
This has worked well up to now.
I am in the throws of replacing the mdb/mde backend with MySQL. The custom search has now become much slower!
I have included small snippets of the search code from different event handlers.
Private rsClone As DAO.Recordset
‘strField – field in tabular form to search by.
Screen.ActiveForm.subForm.Form.OrderBy = strField
'Create clone after datasheet has been sorted...
Set rsClone = Screen.ActiveForm.subForm.Form.RecordsetClone
rsClone.Sort = strFiel
‘strLoc - user entered search string.
If strLoc = "" Then
rsClone.FindFirst strSearch & " LIKE '" & strLoc & "*'"
'Sync current record with clone...
If Not rsClone.NoMatch Then
Screen.ActiveForm!subForm.Form.Bookmark = rsClone.Bookmark
‘show not found message in status bar.
How can I optimize it now that the tables are MySQL InnoDB?
You are using a 'Like' to search. While it works great for small databases it tends to get a bit slow since your using a wilcard character. So it has to read the entire string before moving on to the next record.
You could replace it with LEFT(table.field, #)='strSearch'
Where # is equal to the number of characters entered.
That way your only reading a set amount of characters.