Log Autocompleter Search Queries into Database

[blitzen]

Hi,
Our website uses Ajax.Autocompleter function for a quick search (http://madrobby.github.com/scriptacu...autocompleter/).
I'd like to be able to capture and log the final search term into a database.

After 3 characters, with each character a user types into the search box, AJAX calls an external/server-side search script to do the search.

To log each time the search script is called (which is every time a character is typed after the 3rd one) would increase the size of the table pretty fast.

I can log the time and look at the time difference that the last time that particular IP called the search script, and UPDATE that row instead of INSERT a new row.
However, if that same IP makes a different search query, I'd like to capture and log both queries.

I don't want to slow down the search any more than necessary with more mysql calls. My db is fairly large as it is (over

I could capture each one then cleaning up the table daily, but am stuck for the mysql query.

Does this make sense?
Does anyone have a slick idea how to best log the search results without logging each character entered?

[DaveSawers]

Since the user doesn't have to press return or do anything specific to initiate the search then it's not a simple problem. You have to write something to the database each time a key is pressed. Using timing to try and detect that a search has ended is never going to be reliable.

You could look at each search term, compare it with the previous search term and if it has the same beginning overwrite the previous one in the database, so for example:

User types
H - do nothing
A - do nothing
M - do search, save HAM to database
S - do search , save HAMS to database overwriting HAM
T - do search , save HAMST to database overwriting HAMS
O - do search , save HAMSTO to database overwriting HAMST
backspace - do search , save HAMST to database overwriting HAMSTO
E - do search , save HAMSTE to database overwriting HAMST
R - do search , save HAMSTER to database overwriting HAMSTE

User does something else then deletes this search word and starts another. Since the first three letters of the new search are almost certainly going to be different than before, you can start the same process again.

[blitzen]

Yea - that's what I thought that I'd need to query the DB for each character. I was hoping for something that didn't task the DB any more than absolutely necessary because the search table will grow (archived after so many days and archived data removed from the live table).

I capture the IP and SID. I can then look for searches within a few seconds and overwrite that one.
I've seen people enter characters, backspace to correct spelling or whatever. So, perhaps the timing would work.

User enters ABC - gets inserted.
Enters D - if within, say 10 seconds of an existing entry with same IP and SID, then overwrite that one. Otherwise, insert new row.

Thank you for the thoughts.

[jhannawin]

Can you trigger another ajax call to save the current search term if the user takes focus from the search entry? Or perhaps a certain time after the last keystroke. Otherwise you are potentially hammering the database with updates.