SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Guru aamonkey's Avatar
    Join Date
    Sep 2004
    Location
    kansas
    Posts
    953
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Optimizing a search query

    I've been using a php search class for years - it works really well and uses the levenshtein function to dynamically pull extremely relevant results for any number of DB columns. The problem is is pulls every single row in the database and runs an algorithm on them. This is actually faster than it sounds ( about 0.3 seconds on 30,000 rows), but I was hoping to optimize it by doing it entirely in sql.

    Here is a query that runs extremely fast (about 0.02 seconds on 30,000 rows):

    Code SQL:
    SELECT id, firstName, ((CHAR_LENGTH(firstName) - levenshtein('robert', firstName)) / CHAR_LENGTH(firstName))  AS matchPercentage
    FROM Clients

    The problem is now I need to order those results by the calculated field 'matchPercentage', and as soon as I do that it bogs down to around 18 seconds, i.e.
    Code SQL:
    SELECT id, firstName, ((CHAR_LENGTH(firstName) - levenshtein('robert', firstName)) / CHAR_LENGTH(firstName))  AS matchPercentage
    FROM Clients
    GROUP BY id
    ORDER BY matchPercentage DESC

    I've also tried creating temporary tables, etc but everything seems to run around 18 seconds.

    Is it possible to achieve what I'm wanting efficiently?
    aaron-fisher.com - PHP articles and more

  2. #2
    SitePoint Guru aamonkey's Avatar
    Join Date
    Sep 2004
    Location
    kansas
    Posts
    953
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I am re-reading my post, and to clarify: my php Search class does not pull every row in the database, it pulls every row in a given table. Also, the levenshtein function in the SQL above is a stored procedure.

    So none of this is mysql specific really - it's more of a question of "how do I order results by a DBMS-intensive algorithm?"
    aaron-fisher.com - PHP articles and more

  3. #3
    SitePoint Guru aamonkey's Avatar
    Join Date
    Sep 2004
    Location
    kansas
    Posts
    953
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Guess this is impossible, huh?
    Oh well I can live with the current setup.
    aaron-fisher.com - PHP articles and more


Bookmarks

Posting Permissions

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