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
LVL 1
myyisAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

COBOLdinosaurCommented:
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.


Cd&
0
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
or
2.  You may frequently select using one or the other -- create two indexes.
0
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.
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

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.
0
myyisAuthor Commented:
Field1 and FIELD2 are not Primary Keys seperately. They together form the PK (FIELD1,FIELD2)
Does this make a difference?
0
Cornelia YoderArtistCommented:
If you SELECT only using FIELD2 very frequently, make it the first in primary key list.  That will apply the indexing to both

SELECT ...WHERE FIELD2=....

and to

SELECT ...WHERE FIELD1=...AND FIELD2=...

Whichever field you will SELECT with alone (without the other field) most often should be first in the list.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.