Auto Index in My SQL

Question

I am using MySQL for my database. I have a requirement to store a list of users in the system. The user will have both first name and last name.

I cannot have the first name and second name as the primary key. So I need a indexing key. But I do not want to enter the index value every time to add a new row.

I want the system to handle this with auto increment of previous value every time I try to add a new user. Please let me know how to do this.

Also I am supposed to do a search operation on the list of user with the first name. Please let me know the efficient way to do the search as the number of records could be more than a million.

Answer 1

To get a unique key without having to specify it, you need an auto_increment field:

create table people (
    person_id int primary key auto_increment,
    first_name varchar(50),
    : : :
    );

For efficiently searching first names, you just need an index on the first-name column. A couple of million rows is not a big table, so this should be reasonably efficient.

create index person_index using btree on people (first_name);

Answer 2

create table users (
 id int primary key auto_increment,
 name varchar(64),
 ...
)

See here for more info

Answer 3

Add an INT() id column with auto_increment set. This way, the id column value will be incremented automatically on each INSERT.