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