how many index needed in a table

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
valen53
Forum Contributor
Posts: 137
Joined: Tue Aug 27, 2002 9:29 am

how many index needed in a table

Post by valen53 »

for improve the perforcement, we need to set index in table.
But how many index needed in a table? for example

OTform table
-emp_id
-OT_Date
-OT_id
-strOT
-endOT
-value

sometimes i will select the table where emp_id and ot_date and ot_id.
sometimes i will select the table where emp_id only.
sometimes i will select the table where OT_date only.

Then how many index should i set ?
User avatar
CoderGoblin
DevNet Resident
Posts: 1425
Joined: Tue Mar 16, 2004 10:03 am
Location: Aachen, Germany

Post by CoderGoblin »

Ok I am looking at this from a Postgres view but I would imagine MySQL would be similar

Indexes can benefit SELECT, UPDATE and DELETE commands with search conditions. Indexes can moreover be used in join queries. Thus, an index defined on a column that is part of a join condition can significantly speed up queries with joins.

When an index is created, the system has to keep it synchronized with the table. This adds overhead to data manipulation operations (UPDATE, INSERT). Therefore indexes that are non-essential or do not get used at all should be removed. Note that, (in Postgres not sure on MySQL) a query or data manipulation command can use at most one index per table.

So it ends up a balancing act. Index the primary key for starters as this is the main thing you "should" be looking for. Then look at your SQL statements where you use the WHERE fieldname='Xyz' command, not a SELECT fieldname. (Fieldname being what you need to index).

The three selections:
emp_id only. Should Index
emp_id and ot_date and ot_id. emp_id already indexed, ignore others
OT_date. No other index, if used as part of the WHERE clause index should improve performance.

Hope that helps.
valen53
Forum Contributor
Posts: 137
Joined: Tue Aug 27, 2002 9:29 am

Post by valen53 »

thanks for reply.

i think quite useful.But i not so understand
The three selections:
emp_id only. Should Index
emp_id and ot_date and ot_id. emp_id already indexed, ignore others
OT_date. No other index, if used as part of the WHERE clause index should improve performance.
is it i only make index to emp_id only? b'cos in this table, emp_id + ot_date + ot_id is unique key.
User avatar
CoderGoblin
DevNet Resident
Posts: 1425
Joined: Tue Mar 16, 2004 10:03 am
Location: Aachen, Germany

Post by CoderGoblin »

emp_id,ot_date and ot_id may all be unique keys but you need to ask the question what do I search on?

As previously mentioned "...Note that, (in Postgres not sure on MySQL) a query or data manipulation command can use at most one index per table..." Given the queries you mentioned, the addition of another index (ot_id) does not make sense.

Always remember that indexes speed certain things up but produce an overhead on others. It is all a balancing act. Too many indexes can actually slow things down.
Post Reply