I'm wondering what would be faster to use with Jquery's autocomplete assuming a recordset of about 500,000 rows with a single column name.
Store the rows in memcache and pull out of memory in PHP and do a string comparison.
Or
Use mysql and perform a like '%%' against the search term.
The "PHP solution" sounds a bit like re-implementing the SQL query filter - only to be fair you'd need to use strpos as appropriate and not a simple equality.
The biggest things with SQL/LIKE queries, or really queries in general, is making sure that they are able to utilize indexes - this generally means limiting to foo% filters, building a de-normalized reverse index, using "fulltext" (MySQL itself can only do prefix wildcards), or just being okay with full table scans.
I would expect SQL to be faster overall - although the only way to know for certain is to create a performance benchmark/profile under actual usage conditions - because, even with a full table scan, less data must be transmitted (and the PHP method doesn't sound like it would use an ingenious solution).
On the other hand, memcached is useful for caching the results of such an SQL/LIKE query: say the query is limited to 100 results which are stored in memcached according to the filter value.
If/when the same filter comes from the client, memcached finds an entry based on exact key and doesn't need to go to SQL The cached entry found is already filtered by the underlying SQL/LIKE query and thus can be returned directly. (Then it just becomes a game of making sure that the cache only deals with information that might be queried again in the near future.)
Questions like these are impossible to guess. Saying that, i would guess option 2 would be faster.
I wouldn't make a decision until i had tried both and even then i'd try and come up with a third alternative.
