Page 1 of 1

how many index needed in a table

Posted: Wed Mar 23, 2005 1:22 am
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 ?

Posted: Wed Mar 23, 2005 3:19 am
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.

Posted: Thu Mar 24, 2005 12:46 am
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.

Posted: Thu Mar 24, 2005 3:13 am
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.