Auto-completion for names against a SQL database

Question

I have a text field in my web app where I want to do auto-completion (e.g. the user types "St" and I can suggest "Steve"). The names I'm matching against are in a SQL database table of users. My question is, how can I make this happen in a way that will scale to massive amounts of users?

1. There's DB full text search or something like Lucene. Would that even be appropriate for a "starts with" query like this?

2. Is there a way to set up a normal DB index for "starts with" type searches?

3. Any other ideas that I'm totally missing?

Any help would be appreciated. Thanks.

Answer 1

These should do the job as long as you have an index on the name column.

SQL Server:

SELECT TOP 10 name FROM names WHERE name LIKE 'St%'

MySQL (according to Bart J):

SELECT name FROM names WHERE name LIKE 'St%' LIMIT 10

Oracle:

SELECT name FROM names WHERE name LIKE 'St%' AND rownum < 10

Answer 2

If you have an ordered index on the field you want to autocomplete then it can be used in a "starts with" style query.