I am really a bit lost on why this is happening. I have read a considerable
amount of online info regarding CakePHP and slow queries, but *I don't *believe
its anything wrong with CakePHP. There has got to be a way to speed things
up.
My CakePHP query is below.
Processing of query is considerably slower on new server. (all details
below). I am running a php/MySQL query. I have some peculiar (slow down)
things happening when I transferred the site to another server. *I am
running the exact same php script (and query options) on both servers. The
database tables and data are also identical. *
Test #1 (Server #1): (default) 32 queries took 1 ms
Test #1 (Server #2): (default) 32 queries took 10 ms (9ms longer)
Test #2 (Server #1): (default) 132 queries took 5 ms
Test #2 (Server #2): (default) 144 queries took 60 ms (55ms longer)
To translate the difference into seconds of waiting time, server #1
generally processes any query (meaning results are displayed) within 3
seconds. And server #2 generally 7-14 seconds.
I am sorry to post my entire search() function (which is returning the
results as expected), but if anyone can evaluate it for problem points and
*harsh
*critique I am all ears!
function search($ApplicantAge = 0, $SpouseAge = 0, $NumberChildren = 0,
$Vision = 0, $Zip = 0) {
$search = $this->data['Plan'];
$searchdetails = array();
if (isset($search['ApplicantAge'])) {
$this->Plan->set($this->data);
if ($this->Plan->validates()) {
$ApplicantAge = $search['ApplicantAge'];
} else {
$errors = $this->Plan->invalidFields();
$error_messages = implode(' ', $errors);
$this->Session->setFlash($error_messages);
$this->redirect('/');
}
}
$searchdetails[0] = $ApplicantAge;
if (isset($search['SpouseAge']) && !empty($search['SpouseAge'])) {
$SpouseAge = $search['SpouseAge'];
}
$searchdetails[1] = $SpouseAge;
if (isset($search['NumberChildren']) &&
!empty($search['NumberChildren'])) {
$NumberChildren = $search['NumberChildren'];
}
$searchdetails[2] = $NumberChildren;
if (isset($search['Vision']) && !empty($search['Vision'])) {
$Vision = $search['Vision'];
}
$searchdetails[3] = $Vision;
if (isset($search['ZipCode']) && !empty($search['ZipCode'])) {
$Zip = $search['ZipCode'];
}
$searchdetails[4] = $Zip;
$memberCount = 1;
if ($SpouseAge > 0) {
$memberCount += 1;
}
if ($NumberChildren > 0) {
$memberCount += $NumberChildren;
}
$comboType = 'sa';
if ($ApplicantAge < 18) {
$comboType = 'sc';
}
if ($SpouseAge > 0) {
if ($NumberChildren > 0) {
$comboType = 'asc';
} else {
$comboType = 'as';
}
} else {
if ($NumberChildren > 0) {
$comboType = 'ac';
}
}
$options = array(
'joins' => array(
array(
'table' => 'plans_zips',
'alias' => 'PZips',
'type' => 'inner',
'foreignKey' => false,
'conditions' => array('Plan.id = PZips.plan_id')
),
array(
'table' => 'zips',
'alias' => 'Zips',
'type' => 'inner',
'foreignKey' => false,
'conditions' => array('Zips.id = PZips.zip_id')
)
),
'conditions' => array(
"AND" => array(
array($ApplicantAge . ' BETWEEN Age.Min_Age AND
Age.Max_age'),
'Zips.title' => $Zip,
'Applicant.amount' => array($comboType, $memberCount)
)
),
'contain' => array(
'PlanDetail' => array(
'fields' => array(
'id',
'effective_date',
'expiration_date',
'active',
'name',
'plan_type_id',
'max_benefit',
'deductible',
'preventive',
'basic',
'major',
'ortho',
'application_url',
'company_id',
'plan_type_id',
'plan_detail_note_id'),
'Company' => array('fields' => array(
'id',
'name',
'company_logo_url'
)),
'PlanType' => array('fields' => array(
'id',
'name',
'description'
))),
'Age' => array('fields' => array(
'id',
'name',
)),
'Applicant' => array('fields' => array(
'id',
'name',
)),
'Zip' => array('fields' => array(
'id',
'title')
)
),
'limit' => 100,
'order' => array('monthly_cost' => 'ASC')
);
if ($Vision == 1) {
array_push($options['conditions'], "dental_cost > 0");
}
$url = array('controller' => 'plans', 'action' => 'search');
$this->paginate = $options;
$this->set('searchdetails', array_merge($url, $searchdetails));
$this->set('plans', $this->paginate('Plan'));
$this->set('ApplicantAge', $ApplicantAge);
$this->set('SpouseAge', $SpouseAge);
$this->set('NumberChildren', $NumberChildren);
$this->set('Vision', $Vision);
$this->set('Zip', $Zip);
if ($this->RequestHandler->isPost()) {
$this->Session->write('search_form_sess', $this->data);
$initial_url = $ApplicantAge . '/' . $SpouseAge . '/' .
$NumberChildren . '/' . $Vision . '/' . $Zip;
$this->redirect(array('action' => 'search', $initial_url));
exit();
}
$this->layout = 'default';
}
*MySQL Server Data*
***Server #1***
Server: Localhost via UNIX socket
Server version: 5.0.92-community
MySQL client version: 5.0.92
PHP extension: mysql
Protocol version: 10
----------------------------------------
***Server #2***
Server: Localhost via UNIX socket
Server version: MySQL - 5.0.51a-24+lenny5-log
MySQL client version: 5.0.51a
PHP extensions: mysql
Protocol version: 10
--
Our newest site for the community: CakePHP Video Tutorials
http://tv.cakephp.org
Check out the new CakePHP Questions site http://ask.cakephp.org and help others
with their CakePHP related questions.
To unsubscribe from this group, send email to
[email protected] For more options, visit this group at
http://groups.google.com/group/cake-php
