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 and 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) Received on Sun Aug 14 2005 - 14:05:21 CDT