asmint3

@corbin - thanks. That was just an example, I actually do "where name like 'bob%'" in the real world which is why a trie solution would be much more efficient. Sorry for posting the wrong example! The table is currently ~100k rows and is growing fairly rapidly. The query is slowing as the table grows, it's clearly not a long term solution since it's not going to scale (yes, I do limit the query size as well). @Mark Baker - I think it depends on the site and it's usage. This is a large site and one of the key modes of operation is autocomplete search. Even with a large DB, once a few hundred people start to query it at the same time it doesn't take long for the performance to drop off. From the reading I've done the large sites all seem to have in memory data structures (like a trie) to aid autocomplete.

Hi, I'm implementing an AJAX autocomplete against a fairly hefty database. I started out using "select * from table where name like '%bob%'" but that's running like a dog due to the table size. Apparently the trie data structure is a good way to go to optimise my autocomplete lookup but I haven't found any kind of integration of extension for PHP. If you've no idea what I'm talking about checkout http://en.wikipedia.org/wiki/Trie and http://www.chokkan.org/software/dastrie/ for an implementation. Can anyone advise whether they've seen any use of trie from a php application (Drupal in my case) or any other alternative? Thanks,