Hmm, if you say, have 1.3 million records, that would mean an average of
50000 records for every query - a bit too much for my liking. I'd
recommend that you rather let the user type as much as he wants, and
only when he says 'search', execute the query.

As for index, I'd simply recommend a normal ascending index on an upper-
or lowercased version of the name (having one field with mixed-case,
and another field with the uppercase equivalent populated through
triggers, is a good option). If the field is too long to index, have a
copy containing the beginning of the name.

HTH,
Set

chee hee wrote:

> Hi All,
>
> I have this customer table with the following structure and contains over millions of records.
>
> CREATE TABLE CUSTOMER (
> CUSTOMER_ID BIGINT,
> CUSTOMER_NAME VARCHAR(500),
> .....);
> CUSTOMER_ID is the primary key.
>
> The UI has 26 alphabet characters from A to Z. When user clicks on A, it will list all customers with name starting with A. I guess the query will be:
> select ... from customer where customer_name starting with 'A';
>
> How do I index the table for best performance? Or what is the best way to design such table.
>
> Thanks.
> CM