What are common indexes?

[robertbrown1971]

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)

[Erland Sommarskog]

(ro*************@yahoo.com) writes:

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?
Yes. That is how a PK is implemented. (As is a UNIQUE constraint.)
By default a PK is clustered, but this does not mean that the PK
is always the best choice for the clustered key. For an Employees
table, for instance, the PK should probably be non-clustered.

A good candidate for the clustered index might be last_name or dept_id
(in the case an employee can belong to one department only).
2. Index on foreign keys in the children tables to prevent deadlocks
and lock escalations.

CREATE INDEX fk_index ON employee_addresses (emp_id)
Yes, this is a good idea. Notice that in some products this is
created automatically. This is not the case in SQL Server. And with
a good reason. Say that you have a Countries table, and your
address table includes a country code. There is little reason to
index addresses.countrycode, just because this is a FK reference,
since Countries will be static, and you may never remove any rows
from the table. (If you are search on country code it's another
matter.)
3. Indexes on common queries on all three tables.

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'
The above index is not likely to be useful, because street is not
included. It could still be used, because SQL Server could scan
the index to find all entries. You would need an index with
employee_addresses.city as the first column.
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?
Yes.
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)
Yes.
Question: should I use INDEX ORGANIZED TABLE?
Eh? This is a term, I don't recognize.
Question: should I have UNIQUE qualifier on the second index given that
PK already takes care of it?

Yes.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[robertbrown1971]

Erland Sommarskog wrote:

3. Indexes on common queries on all three tables.

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'
The above index is not likely to be useful, because street is not
included. It could still be used, because SQL Server could scan
the index to find all entries. You would need an index with
employee_addresses.city as the first column.

Erland,

So if I search for city separately and for street separately as well as
together should I define two indexes on city and street individually as
well as a composite index on both?

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?

Yes.

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)

Yes.

Question: should I use INDEX ORGANIZED TABLE?

Eh? This is a term, I don't recognize.

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

Yes.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Erland Sommarskog]

(ro*************@yahoo.com) writes:

So if I search for city separately and for street separately as well as
together should I define two indexes on city and street individually as
well as a composite index on both?

In the most extreme case you would need three indexes: (city), (street)
and (city, street).

But it depends a little on how selective the data is, the volumes etc.
It could very well be sufficient with two indexes.

I'll also like to add that search for street alone in address sounds a
little funny to me, but I have the feeling that the examples your posted
are not your real application, but posted for the question only.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[AK]

>> Question: should I use INDEX ORGANIZED TABLE?
<<

SQL Server does not have them (Erland - IOTs are provided by Oracle),
but a table with a clustered index is pretty much the same

[AK]

>> In the most extreme case you would need three indexes: (city), (street)
and (city, street).
<<

Very interesting. Erland, please give an example when (street, city)
and (city, street) would not suffice.

[Erland Sommarskog]

AK (AK************@hotmail.COM) writes:

In the most extreme case you would need three indexes: (city), (street)

and (city, street).
<<

Very interesting. Erland, please give an example when (street, city)
and (city, street) would not suffice.

If all you want to search for is street, then city will be unnecessary
baggage that will make the index unnecessarily big. Then again, that
would mainly be an issue for queries like:

SELECT street, COUNT(*) FROM tbl GROUP BY street

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[AK]

On one hand, I do agree to what you've just said.
On the other hand, if there is a lot of OLTP activity against the
table, I would *usually* have 2 bigger but reusable indexes on the
table

of course, there are exceptions - there are no hard and fast rules in
our trade..