I'm wondering what source of data would be best for an autocomplete input box (using AJAX). Currently I have it looking for matches in a MySQL database using LIKE, but I'm worried about the multiple, consecutive hits while the person is typing (I do have a delay, but it still could be a considerable number of queries if multiple people are typing at the same time). Would searching a flatfile (CSV or XML) be any more efficient, or is the DB the way to go? If flatfile would be better, what format would you suggest (any good functions/classes out there)? If DB is the better route, is LIKE ok, or would fulltext be better? We're talking a little over 2,000 records to search a title field, currently.

Thanks!

Matt

    A db will always be faster, its what they are designed for.

      nurseryboy wrote:

      We're talking a little over 2,000 records to search a title field, currently.

      A MySQL DB running a table with a million rows wouldn't choke and die, so 2k+ rows isn't really that much to lose sleep over. 😉

        Haha, ok. I just wasn't sure with it making multiple, consecutive queries while a person is typing. If a word is 10 characters long, that's 10 queries, all right after each other. Multiply that by however many people may be doing it at the same time, and I just wanted to make sure it wouldn't be a performance issue.

          I thought you mentioned having a delay? E.g. it would only check the text field's contents and query the DB (if changed) every x seconds (e.g. 1 or 2)? Does it even check if the field's contents have actually changed after that delay before deciding whether or not to re-query the DB?

            Yeah, I do have a delay. It's only a few hundred ms, though (as any more hurt the usability). There's also a 2 character limit on it, so it's not running queries for anything with an "a" in it (for example). I also have it "cache" the results (just in js arrays), so if the person types in the same thing (or continues with a word that has no results), it doesn't have to run the query again (provided the page hasn't reloaded and the js array hasn't been lost). As you can see, I'm very conscious of keeping the overhead/processing down, thus the inquiry of what datasource would be best.

              Well, none of what you describe would be done differently whichever storage method you used, so the decision would be based on whether it would be better to use a database management system to manage your database or not.

                Write a Reply...