You know on Twitter, when you create an account, it does an async* SQL query with every keystroke to see if that account doesn't already exist? Well, my client wants that. I'm postponing that for now so that I can knock out other features, but I wanted to at least get the discussion going with you guys to see if you are familiar with it.
* Async, as opposed to sync. With async, multiple transactions can run at the same time, in parallel. With sync, each transaction is blocked until the previous one is completed, serially, in order.
To me, it means that with every keystroke in the username field on Twitter, they have to make an async AJAX SQL query (likely using jQuery to help) back to the database. When someone starts typing again in the username field, the response handler would have multiple responses coming back. They basically have to reject multiple responses and take the latest response. They probably use some interval timer so that they don't send the query until 0.5 seconds when someone stops typing. I also see that this is modeless -- while the query is running in the background, they let one keep on typing.
And it's probably not good to check on the most recent request because one of those async calls may come back out of order if the server receives an unusual performance spike. So, you basically have to tag an async AJAX request with a key field, and then keep parsing requests until you find the one with the latest key field that comes back from the server.
I would think that a control like this on a web page would create a significant impact on database performance. So, to help offload that, the interval timer in the Javascript helps, but it's not the only help one needs. Next, you would probably need an exclusive table in the database just for this, and it contains no other columns but this column. It then needs a properly tuned index against it. And then there might be a need to do some sort of either pure RAM cache on this, or tmpfs (virtual memory) cache. And having the query run on one of these new SSD drives (solid state drives) might improve performance as well. And I would definitely say -- this is not something you want to implement on a shared hosting plan. It takes a dedicated hosting plan for such a thing.
Suggestions?
Async Query on Keystroke (a.k.a "autocomplete")
Moderator: General Moderators
- volomike
- Forum Regular
- Posts: 633
- Joined: Wed Jan 16, 2008 9:04 am
- Location: Myrtle Beach, South Carolina, USA
Async Query on Keystroke (a.k.a "autocomplete")
Last edited by volomike on Fri Jan 09, 2009 12:22 pm, edited 1 time in total.
Re: Async Query on Keystroke
This feature is called "autocomplete", and if you are using jQuery there are plenty of available plug-ins:
http://www.google.co.il/search?q=jquery+autocomplete
Depending on the size of your data, it might be better to cache it into a javascript file (as a JSON structure) and read directly from that instead of querying the server repeatedly
http://www.google.co.il/search?q=jquery+autocomplete
Depending on the size of your data, it might be better to cache it into a javascript file (as a JSON structure) and read directly from that instead of querying the server repeatedly
- volomike
- Forum Regular
- Posts: 633
- Joined: Wed Jan 16, 2008 9:04 am
- Location: Myrtle Beach, South Carolina, USA
Re: Async Query on Keystroke
The JSON cache idea would work, but here's the catch. It's a 2.5MB download if you have 100,000 names, each about 25 chars in length. But, if I use AJAX to look at the first two characters and then download an exclusive JSON cache just for names starting with those two characters, that's a different story entirely. That download might be as little as 2,000 names (roughly 30K, the size of a typical image on a website).
Re: Async Query on Keystroke (a.k.a "autocomplete")
In the link pytrin posted, they used both AJAX and cache - they wait until 2 chars are entered and after that they fetch ALL cities begining with these two letters.
There are 10 types of people in this world, those who understand binary and those who don't
- volomike
- Forum Regular
- Posts: 633
- Joined: Wed Jan 16, 2008 9:04 am
- Location: Myrtle Beach, South Carolina, USA
Re: Async Query on Keystroke (a.k.a "autocomplete")
Ah, fantastic. They think like I do. Makes sense to do it that way.
Re: Async Query on Keystroke (a.k.a "autocomplete")
Your particular problem is even easier to solve because usually a minimum length of 4 chars is required for usernames which will greatly reduce the size of usernames cache set.
There are 10 types of people in this world, those who understand binary and those who don't