What Are Common Indexes?

I just inherited a Java application with a fairly complex data model
that does not yet have any indexes except those on primary keys. It is
still in development and before I get to do any performance testing I
want to put some default indexes on it. I realize that this a very
vague request but it's better than nothing so I want to start with
those that are generally a good idea and then tune those areas that
require more fine grained approach. By the way, I'm mostly a Java
programmer so please forgive my DB ignorance and I thank you for any
help.

Since the full schema is quite large I will use a simple example (fully
cited below my questions). Here is list of indexes that I think would
be a good idea. If anyone can add to it or comment on my questions I
would appreciate it.


1. Index on primary keys in all three tables. My understanding that
this indexing happens automatically just by declaring that a column is
a PK.

ALTER TABLE employees ADD PRIMARY KEY (emp_id);
ALTER TABLE employee_addresses ADD PRIMARY KEY (address_id);
ALTER TABLE departments ADD PRIMARY KEY (dept_id);

Question: Does index get created automatically because this is a PK?


2. Index on foreign keys in the children tables to prevent deadlocks
and lock escalations.

CREATE INDEX fk_index ON employee_addresses (emp_id)


3. Indexes on common queries on all three tables.

CREATE INDEX common_query_idx on employees(last_name, first_name,
position)


CREATE INDEX common_query_idx on departments(last_name, first_name,
position)


CREATE INDEX common_query_idx on employee_addresses(street, city)


Question: Given that the searches can be on any field separately and
in any combination should I also put an index on each column
individually or will the composite index take care of individual
searches as well? For example, will the above indexes be sufficient
for the following SELECT:

SELECT e.last_name, e.first_name from employees e, departments d,
employee_addresses ea, dept_employees de WHERE e.emp_id = de.emp_id AND
d.dept_id = de.dept_id AND ea.emp_id = e.emp_id AND e.position =
'master chief' AND d.dept_name = 'galactic affairs' AND ea.city='Los
Angeles'


4. Unique index on the association table. Again this is accomplished
using PK

ALTER TABLE dept_employees ADD PRIMARY KEY (dept_id, emp_id)

Question: Is the index on (dept_id, emp_id) automatic because of PK?

5. The association table has to go both ways and PK takes care only of
the first half. Thus add an index to go the other way.

create unique index dept_employee_idx on dept_employee(emp_id,dept_id)

Question: should I use INDEX ORGANIZED TABLE?

Question: should I have UNIQUE qualifier on the second index given that
PK already takes care of it?


Thanks,

Robert


===== EXAMPLE ======


1) An employee can be in many departments and a department can contain
many employees.

2) Common searches for employees are on last_name, first_name,
position, department_name, department_location separately and in any
combination.

3) There are common searches for departments that contain certain
employees e.g. find all departments containing John Smith.


CREATE TABLE employees
(
emp_id INTEGER NOT NULL,
last_name VARCHAR(50) NOT NULL,
first_name VARCHAR(25) NOT NULL,
position VARCHAR(10) NOT NULL
);


CREATE TABLE employee_addresses
(
address_id INTEGER NOT NULL,
emp_id INTEGER NOT NULL,
street VARCHAR(50) NOT NULL,
city VARCHAR(25) NOT NULL,
);


CREATE TABLE departments
(
dept_id INTEGER NOT NULL,
dept_name VARCHAR(50) NOT NULL,
dept_location VARCHAR(25) NOT NULL,
);


CREATE TABLE dept_employees
(
dept_id INTEGER NOT NULL,
emp_id INTEGER NOT NULL,
);

ALTER TABLE employee_addresses ADD FOREIGN KEY (emp_id) REFERENCES
employees(emp_id)

ALTER TABLE dept_employees ADD FOREIGN KEY (emp_id) REFERENCES
employees(emp_id)

ALTER TABLE dept_employees ADD FOREIGN KEY (dept_id) REFERENCES
departments(dept_id)