i've just created a table in mysql with 3 fields

below the table it says
No index defined!

Should i define an index?
should i have a primary key too?
and why do i need them?

Indexes are required on auto_increment fields, but are often helpful on others. MySQL uses the index to quickly find the rows that fulfill your WHERE clauses (otherwise it needs to read the entire table). The benefits increase as your tables and queries get larger, but I've seen improvements in even 20 rows or so. On larger tables (thousands of rows), the difference can be orders of magnitude.

I suggest buying a copy of MySQL (2nd edition) by Paul Dubois. It has some good coverage of indexing as well as just being a handy desk reference.

You should always properly index fields in a database that you will be searching on or joining against.

You should also always define a primary key in most cases. An integer with auto_increment is best for most cases.

The index is used for fast lookups. The primary key is used as a unique identifier, used to update or link records.

ok thanks, at the moment i have 3 fields , i'll add another one called id set it to auto increment and make it primary key

i'll be doing queries on all the other fields so what do i set them as?

a standard index.

Originally posted by saghir69
i'll be doing queries on all the other fields so what do i set them as?

the 2 other fields.. it depend i guess

if you do a lot of SELECT WHERE lookups the index(es) speed things up a lot. downside of indexes is that update/insert queries can be a little bit slower since the table need to re-index

I would go for normal fields, unless its too slow, if needed you can always add/remove indexes at a later time in mysql.

Primary key is essential. When you create a table it's usually to models an entity into data. Primary key allow you to have a unique identifier for your entity. Each customers have a unique customer id, right ? Thats the primary key, if that can help you understand the gimmick.

As for indexes. like Vult-r said, I would suggest you add them when they are really needed.

Cheers,
Matt