how to create index

I have a query like SELECT * FROM TABLE WHERE FIELD1='bla bla' AND FIELD2='etc etc'

1. Which is better, creating an index with 2 colums (FIELD1, FIELD') or creating 2 separate indexes for each field?
2. If  is better to create 1 index with 2 columns and let's say FIELD1 is the primary key of the table what should I do (Since there is already a unique index for FIELD1) ?

Thank you
Who is Participating?
Cornelia YoderConnect With a Mentor ArtistCommented:
If you SELECT only using FIELD2 very frequently, make it the first in primary key list.  That will apply the indexing to both


and to


Whichever field you will SELECT with alone (without the other field) most often should be first in the list.
Based on the vague description given, I would prefer to used two indices as it gives more flexibility at the cost of slightly higher overhead.  However that is a very general observation.  The actual design of a database requires detail about access metrics, update frequencies, and the nature of the data.

Cornelia YoderArtistCommented:
If FIELD1 is a primary key, it is already indexed, so you can just create an index for FIELD2.

If not, then decide if

1.  You will always select using both FIELD1 and FIELD2 together -- create one index
2.  You may frequently select using one or the other -- create two indexes.
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

myyisAuthor Commented:
Ok I got it.
What if I have a primary key with 2 fields (FIELD1,FIELD2)  and if I usually select using only FIELD2.

In this case I already have a unique index (FIELD1,FIELD2).  Do I need to have an extra index for FIELD2?

Thank you.
Cornelia YoderArtistCommented:
If FIELD1 and FIELD2 are both primary keys, you do not need to create any INDEXes.  Selecting by either or both of those fields will be using index methodology.
myyisAuthor Commented:
Field1 and FIELD2 are not Primary Keys seperately. They together form the PK (FIELD1,FIELD2)
Does this make a difference?
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.