Re: What are common indexes?

<robertbrown1971_at_yahoo.com> wrote in message news:1124046321.485296.137510_at_f14g2000cwb.googlegroups.com...
> 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)
>

Yes on primary keys and create indexes on foreign keys. IOT's (Index Organized tables) are best used when you are going to retrieve data on part of a multi part primary key. The classic case is historic stock prices. Where the stock symbol and date/time are the primary keys. If you are usually retrieving a particular stock prices by symbol then the rest of the data will be "near" each other. You are going to have to measure the diffecences in your application.

In an OLTP application you don't want indexes on everything. Each index makes updates and inserts take longer. Usually an compound index is valuable when all or the "front" of the index is supplied. For example, to search for city,state then an index on city state would work. To search just for city then city state would still work. To search for state then city state would not be effective. (state, city would, but not in the first case) In the city state example an index on city and state is probably better than 2 seperate indexes, one one city and one on state. Since state will meet a lot of entries then a full table scan might actually be faster. (It depends.)

For the many to many then the relationship table would need 2 indexes, one each way. One thing that might be interesting to try is an IOT for the relationship table. (making the primary key the more common query) and making an index on just the other half of the reverse. (eg create an IOT table on department id and employees' id and then index employees' id)

As always benchmark it and your MMMV.

Jim Received on Sun Aug 14 2005 - 15:57:02 CDT