[GENERAL] The database is very slow !

[krystoffff]

I currently have PostgreSQL 7.1 installed on a server with about 700
Mb of RAM.

I have many problems of speed with a database I created. For example,
it took almost 12 sec to run the query "select * from table" directly
from PostgreSQL, on a table with 4000 records and 60 fields ...
And the whole application built on this database is very very slow
(some pages take almost 20 seconds to load !)

I verifed the indexes, I think they are ok, and I tried to make my
queries as short as possible (without select *  but with select
field1, field2, ...)

But anyway, I guess there is a problem of speed directly with the
database, because I think that is not normal to need 12 sec to run a
query on a table with only 4000 records ...

Has anybody an idea ?
Thanks
Krysto

---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

[GENERAL] optimisation of a code

[krystoffff]

Hi all

I wrote a script in PHP with mysql (hum ... to be honnest, I wrote
this script under PostGreSQL but we recently migrated to mysql, so I
had to adapt my code to mysql ... sorry about that ... anyway, it is
the same kind of query, with subqueries !) but this is a very very
long script ...

I was wondering if there is a way to optimize this one, by doing some
JOIN (because the subqueries don't seem to be very powerful ...)

Here is the script :

To resume, it is a script that :
 - list all the leads available (1st query)
 - For each lead, find 3 members that can buy this lead (2nd query)
 - For each member, buy the lead


";

$today_midnight = strtotime(date('Y-m-d 00:00:00'));

if ($AFF_FIXED_AMOUNTS)
$amount_fixed = $AFF_SHD_AMOUNT;
else
$amount_fixed = $AFF_PERCENTAGE * .01 *
$PRICE_POINT_IN_DOLLARS;


while ($lead=mysql_fetch_assoc($r_avail))
{

$n = $lead[period];
if ($lead[loan_type] == "Refinance") $type="refi";
else if ($lead[loan_type] == "Purchase") $type="pur";
else $type = "homeq";
$field = $type."_t$n";
$price = $lead[price];
$id = $lead[id];
$aff_id = $lead[affiliate_id];

// SECOND QUERY
// find the members that fit all the required criterias
$q_members = "select member.id, automated.delivery, member.email
from (automated INNER JOIN member ON member.id = automated.member_id)
";
$q_members .= " where activated=1 ";
$q_members .= " and website='$SITE_NAME'";
$q_members .= " and (select count(*) from trans_member where
(unix_timestamp(now())-unix_timestamp(date)) <
(unix_timestamp(now())-'$today_midnight') and type='purchase' a\
nd comment LIKE '%automated%' ";
$q_members .= "   and member_id=member.id and comment LIKE
'%$type%') < max_$field ";
$q_members .= " and balance_in_points > $price ";
$q_members .= " and credit_ratings_t$n LIKE
'%$lead[borrower_credit_rating]%' ";
$q_members .= " and states LIKE '%$lead[prop_state]%' ";
$q_members .= " and ltv_t$n/100 >= (cast($lead[loan_amount] as
unsigned) / cast($lead[current_value] as unsigned)) ";
$q_members .= " and amount_t$n < $lead[loan_amount] ";
$q_members .= " and $id NOT IN (select lead_id from purchase where
member_id=member.id) ";
$q_members .= " AND $aff_id NOT IN (select affiliate_locked_id
from affiliate_lockout where member_id=member.id) ";
$q_members .= " AND $id NOT IN (select lead_id from purchase where
member_id IN (select member_id_to_exclude from member_exclusion where
member_id=member.id))";
$q_members .= " ORDER BY balance_in_points DESC";
$r_members = mysql_query($q_members);

$nbdispo = $NBPERSONS_SHARED - $lead[nbsold];

while (($member=mysql_fetch_assoc($r_members)) && $nbdispo>0)
{

BUY THE LEAD FOR THIS MEMBER
$nbdispo--;

}


//}
} // END OF while ($lead=mysql_fetch_assoc($r_avail))


?>

Has anybody an idea ?
Thanks very much for your help
Krysto

---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

[GENERAL] How to get the total number of rows with a query "limit" ?

[krystoffff]

Hi

I would like to paginate the results of a query on several pages. So I
use a query with a limit X offset Y to display X results on a page,
ok.

But for the first page, I need to run the same query with a count(*)
to know how many pages I will get (number total of rows/ X).

The problem is my query is very slow (maybe 5s) because there is much
worch to do, and on the first page, I need to run this query twice
(not exactly, but ...) so the page is very very slow to load.

My question is : is there a function to get the total number of rows
even on a query with "limit" ? Or what could I do else ?

Has anybody an idea ?
Thanks for the help
Krysto

---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])