473,561 Members | 3,656 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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_addres ses 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_addres ses (emp_id)
3. Indexes on common queries on all three tables.

CREATE INDEX common_query_id x on employees(last_ name, first_name,
position)
CREATE INDEX common_query_id x on departments(las t_name, first_name,
position)
CREATE INDEX common_query_id x on employee_addres ses(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_addres ses 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_i dx on dept_employee(e mp_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_loca tion 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_addres ses
(
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_addres ses ADD FOREIGN KEY (emp_id) REFERENCES
employees(emp_i d)

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

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

Aug 14 '05 #1
7 1962
(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_addres ses 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_addres ses (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.count rycode, 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_id x on employee_addres ses(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_addres ses 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_addres ses.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_i dx on dept_employee(e mp_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****@sommarsk og.se

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

Aug 14 '05 #2
Erland Sommarskog wrote:
3. Indexes on common queries on all three tables.

CREATE INDEX common_query_id x on employee_addres ses(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_addres ses 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_addres ses.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_i dx on dept_employee(e mp_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****@sommarsk og.se

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


Aug 15 '05 #3
(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****@sommarsk og.se

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

Aug 15 '05 #4
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

Aug 15 '05 #5
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.

Aug 15 '05 #6
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****@sommarsk og.se

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

Aug 15 '05 #7
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..

Aug 15 '05 #8

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
1895
by: Paul | last post by:
I'm confused about something I did this week. I have an 8.1.7.4 database with tables partitioned by dates. I recently dropped my maxvalue tablespace, added a few more partitions for up-coming dates, then put the maxval partition back on. (I save the data that was in the maxval partition first and put it back in when I was done. These tables...
2
4958
by: Chris | last post by:
Any help would be appreciated. I am running a script that does the following in succession. 1-Drop existing database and create new database 2-Defines tables, stored procedures and functions in the database 3-Imports data using bulk insert 4-Analyzes data using stored procedures I would like to improve the performance of the analysis...
9
1656
by: Igor | last post by:
Is there a way to force optimizer to use indexes without hints? (some server setting or index type...) I'll give an example to clarify : I have a table with fields Customer_Code char(10) not null Invoice_Number int not null and an index on those fields IX_1. there are about 2,000,000 records in the table and those two fields are
9
1880
by: pheonix1t | last post by:
hello, I've been assigned to do performance tuning on an SQL2000 database (around 10GB in size, several instances). So far, I see a single RAID5 array, 4CPU (xeon 700MHZ), 4GB RAM. I see the raid5 as a bottleneck. I'd setup a raid 10 and seperate the logs, database and OS(win2k). The one thing that was a bit odd to me was that I was...
4
10549
by: serge | last post by:
I ran into a table that is used a lot. Well less than 100,000 records. Maybe not a lot of records but i believe this table is used often. The table has 26 fields, 9 indexes but no Primary Key at all! There are no table relationships defined in this database, no Natural keys, only Surrogate keys in the database. 1- Maybe an odd question...
14
2353
by: uli2003wien | last post by:
Dear group, we are running a SQL-Server Database which is about 30 GB large. The purpose of this database is to contain periodic data from automatic devices which insert values into some tables. Unfortunately most of these tables don't have a key (and a key can only be introduced when the application programmers have changed their...
4
1813
by: robert | last post by:
my colleagues are convinced that having more than an index or two destroys performance on their batch runs. could be. i don't have the data, and i'm wondering whether there might be some publicly available. haven't found such. we have a new requirement, which goes about like this. single table access. in the jsp part, the users get to...
5
3200
by: Jim Carlock | last post by:
I've set up the following using an Alias in Apache... Alias /phpdocs/ "C:/Apache/htdocs/common/docs/php/" <Directory "C:/Apache/htdocs/common/docs/php"> Options Indexes FollowSymlinks MultiViews AllowOverride None Order allow,deny Allow from all </Directory>
0
7546
MMcCarthy
by: MMcCarthy | last post by:
The more data you include in your tables the more you will need to have indexes to search and sort that data. However, there is a balance between having enough indexes and too many. Too many indexes will slow down the speed of updates on your records. Access presets a number of Indexes for you. If you look in Tools ... Options under the...
0
7647
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
0
7570
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
0
7859
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
1
7618
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
1
5472
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
5187
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
0
3617
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
1
1181
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
0
896
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.