db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Sai Pullabhotla" <sai.pullabho...@jmethods.com>
Subject Need Advice on Indexes
Date Wed, 24 Dec 2008 12:23:48 GMT
Dear Developers,

I currently have the following table and index in a Derby database.

create table job(
	job_id bigint not null,
	proj_id integer not null,
	proj_name varchar(50) not null,
	proj_folder varchar(256) not null,
	submit_user varchar(20) not null,
	run_user varchar(20) not null,
	queue_priority integer not null,
	run_priority integer not null,
	submit_time timestamp not null,
	start_time timestamp not null,
	end_time timestamp,
	time_taken bigint,
	job_log varchar(256) not null,
	status char(1) default 'A' not null,
	remarks varchar(512),
	sched_job_id integer,
	
	primary key(job_id),
	check(status in('A', 'S', 'F', 'C'))
);

create index idx_job on job (proj_id desc, proj_name desc, proj_folder
desc, start_time desc, end_time desc, run_user desc, time_taken desc,
status desc, sched_job_id desc);

This table would typically have a few hundred thousand records
(~500,000). Records should be searchable by a combination of date
range (start_time), project name, project folder, run user, status
(one or more status codes using IN clause). To further clarify this
search criteria, date range is always required and the rest of the
parameters are optional.

There is also a need to search records by job_id alone as well as
sched_job_id alone. In the first case, there will be at most one
result, but in the second case there could be several.

The search results should be sortable pretty much by any column except
remarks and job log.

We also have a need to display the job count based on the status code
for today, yesterday, last week and last month. In other words, the
last week statistics would show how many jobs were successful, how
many were failed etc. in the past one week.

I'm wondering if the above index I've is good enough to perform all
these functions fast enough. Please advise.

Regards,

Sai Pullabhotla
Phone: (402) 408-5753
Fax: (402) 408-6861
www.jMethods.com

Mime
View raw message