Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2004
    Location
    UK
    Posts
    15

    Unanswered: Optimize msAccess / MySQL search

    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.


    Code:
    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.MoveFirst
    Else
       rsClone.FindFirst strSearch & " LIKE '" & strLoc & "*'"
    End If
        
    'Sync current record with clone...
    If Not rsClone.NoMatch Then
       Screen.ActiveForm!subForm.Form.Bookmark = rsClone.Bookmark
    Else
       ‘show not found message in status bar.
    End If
    
    …
    How can I optimize it now that the tables are MySQL InnoDB?

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Mar 2004
    Posts
    118
    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.

    Aside from that, I dont know.

  4. #4
    Join Date
    Mar 2004
    Location
    UK
    Posts
    15
    That has speeded things up abit - thanks. But things are still slow.

    Is it true that indexes cannot be utilized on multi-table recordsets?

  5. #5
    Join Date
    Mar 2004
    Location
    UK
    Posts
    15
    rsClone.MoveLast
    rsClone.MoveFirst

    ...has done the trick.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •