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? There's DB full text search or something like Lucene. Would that even be appropriate for a "starts with" query like this? Is there a way to set up a normal DB index for "starts with" type searches? Any other ideas that I'm totally missing? Any help would be appreciated. Thanks.

Accepted Answer

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
```

Auto-completion for names against a SQL database

Solution

Problem