Re: [GENERAL] Query time is too long for netscape

Mark Jewiss wrote:
>
> Hello,
>
> On Tue, 25 Jan 2000, Chairudin Sentosa Harjo wrote:
>
> > The query looks like this:
> > select a.custnum, b.fullname, b.address, a.usages from
> > Detail a, Customer b
> > where a.custnum=b.custnum;
>
> Have you got any keys setup on these tables? That would help the query a
> lot.
>
> Mail out your table definitions (and the script) and then we can have a
> look.

I do have a lot of index combinations to help speed up the query,
however
it still takes too long. Here is the script and the table definitions.

SCRIPT
$sql = "select a.pin, b.first_name, b.last_name,
start_time, duration,
country_code, area_code, phone_number,
a.service_type, total_units
from tbs_billing_record a, ibs_subscriber b
where a.pin=b.pin
and date(start_time) between '$begindate' and '$enddate'
and a.service_type='$service_type'
order by $sortby1,$sortby2";

Pg::doQuery($conn,$sql,\(at)bilrec);

The values of $begindate, $enddate, $service_type, $sortby1, $sortby2
are given by user, through HTML form using CGI script.

TABLE DEFINITION
create sequence tbs_br_seq
increment 1
minvalue 0000000001
start 1;

create table tbs_billing_record
(
tbs_br_seq int DEFAULT nextval('tbs_br_seq') NOT NULL,
pin varchar(128) NOT NULL,
start_time datetime,
duration integer,
service_code varchar(2),
country_code varchar(3),
area_code varchar(3),
phone_number varchar(24),
service_type varchar(1),
total_units float,

);

create index tbs_br_pin_idx on tbs_billing_record (pin);
create index tbs_br_start_time_idx on tbs_billing_record (start_time);
create index tbs_br_duration_idx on tbs_billing_record (duration);
create index tbs_br_country_code_idx on tbs_billing_record
(country_code);
create index tbs_br_total_units_idx on tbs_billing_record (total_units);
create index tbs_br_pin_start_time_idx on tbs_billing_record (pin,
start_time);
create index tbs_br_pin_duration_idx on tbs_billing_record (pin,
duration);
create index tbs_br_pin_total_units_idx on tbs_billing_record (pin,
total_units);
create index tbs_br_start_time_duration_idx on tbs_billing_record
(start_time, duration);
create index tbs_br_tbsbrseq_idx on tbs_billing_record (tbs_br_seq);
create index tbs_br_pinstarttimeservicetype_idx on tbs_billing_record
(pin,start_time,service_type);
create index tbs_br_starttimeservicetype_idx on tbs_billing_record
(start_time,service_type);

create table ibs_subscriber
(
id integer,
last_name varchar(30),
first_name varchar(20),
mi_name varchar(1),
password varchar(20),
pin varchar(128),
);

create index ibs_subs_id_idx on ibs_subscriber (id);
create index ibs_subs_last_name_idx on ibs_subscriber (last_name);
create index ibs_subs_first_name_idx on ibs_subscriber (first_name);
create index ibs_subs_password_idx on ibs_subscriber (password);
create index ibs_subs_last_first_name_idx on ibs_subscriber (last_name,
first_name);
create index ibs_subs_id_password_idx on ibs_subscriber (id, password);
create index ibs_subs_pin_idx on ibs_subscriber (pin);
create index ibs_subs_pin_flname_idx on ibs_subscriber
(pin,first_name,last_name);

To print out the detail I use this (part of the long script):

foreach $record (@bilrec)
{
($pin,$first_name,$last_name,$start_time,$duration,
$country_code,$area_code,$phone_number,$service_type,
$total_units)=(at)$record;

$new_duration = ($duration/60);
($front,$back) = split(/\./,$new_duration);
$new_back = substr($back,0,2);
$new_duration = $front.'.'.$new_back;

$no = $no + 1;
$fullname=$first_name.' '.$last_name;
push(@rows,
td([$no,$pin,$fullname,$start_time,center($new_duration),
$country_code,$area_code,$phone_number,
$service_type,
$total_units]));
}

print table
({-border=>'', -cellspacing=>3, -cellpadding=>3},
TR(\(at)rows)

Regards,
Chai