By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,933 Members | 1,676 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,933 IT Pros & Developers. It's quick & easy.

Help with indexing!

P: n/a
Hi,

I have two tables with the following DDL:

CREATE TABLE job (
job_pr SERIAL PRIMARY KEY,
jobid INTEGER,
job_name VARCHAR(25),
owner_name VARCHAR(25),
project_name VARCHAR(25),
software VARCHAR(50),
ctime timestamp,
etime timestamp,
);

CREATE TABLE stats (
jobstatid SERIAL PRIMARY KEY,
jobid INTEGER,
fetchtime timestamp NOT NULL,
mtime timestamp,
status CHAR,
empname VARCHAR(20),
time VARCHAR(20),
used_mem VARCHAR(15)
);

In the 'stats' table, I have around 1 million rows. I'm new to
databases so I didn't know we had to create 'indexes' to make results
faster. I recently created on on the second table by:

CREATE INDEX stat_index1 ON stats (jobid);

Well, it searched faster now but the whole table turned upside down!
Every second row was being repeated and it turned out to be a mess. I
then deleted the index and everything went back to normal. What is
happening? Can't I use an index on this table? Why are my rows being
sorted/duplicated/messed-up? Please help! I need to optimize my
database because it takes a minute before it fetches me anything.
Thanks

Steve
Nov 22 '05 #1
Share this question for a faster answer!
Share on Google+

This discussion thread is closed

Replies have been disabled for this discussion.