Optimize Query

[fnbcprog]

Thanks for checking this out. Just a little background on the program. This is a company database devoted to storing data such as policy information, agent data, and client data. What I am doing in this particular program is searching a database based upon what a user entered in the search fields. There are 3 options: name,policy number, ssn.

At the top of the file there is a routine that checks to see which on was entered and executes the appropriate query. If the number of records returned is greater than 1 then the user is redirected to a results page where all records are printed out through another select query by passed $_GET variables from their search criteria. This is where I have a problem. The whole program takes like 10 or 20 secs just to return 25-30 records to the results page.

From here the user selects one of the returned records and returns back to the index where information is retrieved and other pages are populated.

If there is only one the program continues down and executes another query retrieving all the pertenent information.

Take a look at the script I have attached below.

Thanks for your help!!!!!!!!

[John_Canyon]

How many records are we talking about?

Also would be helpful for you to post your database structure.
I have a feeling you will need to create some indexes in order to speed up your search.

[kevinsequeira]

//just have one page to which the form submits and displays the results, on that page have this
$name = strip_tags($GET['name']);
$ssn = strip_tags($GET['ssn']);
$pol_num = strip_tags($_GET['pol_num']);
$query = "SELECT nbcmdf.*,nbpmdf.id_nbpmdf,nbpmdf.id_nbamdf,nbpmdf.pol_num FROM nbcmdf LEFT JOIN nbpmdf ON nbcmdf.id_nbcmdf = nbpmdf.id_nbcmdf WHERE ";
if($name!="")
$query_where = "nbcmdf.name LIKE '$name%'"";
if($ssn !="")
$query_where = "nbcmdf.ssn LIKE '$ssn%'"";
if($pol_num!="")
$query_where = "nbcmdf.name LIKE '$pol_num%'"";

$query = $query.$query_where;

// now display the results on this page itself , no need to redirect to another page

I didnt understand the client_ID bit in ure code cause the comments says getting it from results.php and i dont see how it gets it
But u get the general idea of how to go abt optimizing ure code rather than having 3 if conditions
and then redirecting to another page
reg
kevin

[fnbcprog]

Number of records: 65000

nbcmdf table:

id_nbcmdf int(10) NOT NULL PRIMARY KEY AUTO_INCREMENT,
ssn char(11),

name char(25) NOT NULL

nbpmdf table:

id_nbpmdf bigint(10) NOT NULL PRIMARY KEY AUTO_INCREMENT,
id_nbcmdf int(10) NOT NULL,
id_nbamdf int(10) NOT NULL,
add_date DATE,

app_date DATE,

eff_date DATE,

stmnt_date DATE,

paid_to DATE,

susp_date DATE,

over_pt char(1),

status char(6),

co_code char(3),

co_code2 char(3),

product char(10),

pc char(2),

face float(10,2),

tprem float(10,2),

mode int(2),

pol_num char(13) NOT NULL,

zip int(5),

chk_no char(8),

age int(3),
reissue char(1),

update1 char(8),

lives int(4),

cwa float(10,2),

escrow float(10,2),

adv float(10,2),

notes char(60)

theres the table structures. Notice that there are multiple policies for each policy holder which is why it is split into two tables.

[John_Canyon]

Ok, assuming you have some way of modifying and optimizing your tables. IE. Access to mysqld, or phpMyAdmin etc.

What I would do is try created some indexes on the fields that you are constantly searching on.

First i would create an index for name and ssn,
If these 2 fields are ALWAYS unique you might try
changing those fields to UNIQUE fields other than
an index.

ALTER TABLE yourtable ADD UNIQUE (name,ssn);

then i would create an index for
nbpmdf.id_nbcmdf

Let me know how it turns out. I think you should notice
significant improvement on speed.

Cheers

P.S. You might consider changing ssn to INT(9) and formatting the string later when you need to display it.

$ssnum = substr($ssn,0,3) . "-" . substr($ssn,3,2) . "-" . substr($ssn,5,4);

[fnbcprog]

there is a certain format for the results page that includes a bunch of other stuff

[fnbcprog]

i already have indecies on those fields. Thats why I am so confused

[John_Canyon]

Please edit your database structure and add INDEX next the fields you have already indexed.

Also I am looking more at your script. Will post something up in a second, we will see how that goes.

[John_Canyon]

I can see why you would do a LIKE search on someones name, but why do a LIKE search on a SSN or Policy Number?

This doesnt make sense to me.

First off i would change your syntax down to =ssn and =pol_num
This should speed things up a bit, also I really see no need for so
many queries to your database? You should be able to accomplish everything with one query per option.

			$result = mysql_query("SELECT bc.*,bp.id_bpmdf,bp.id_nbamdf,bp.pol_num 
								   FROM nbcmdf  as bc LEFT JOIN nbpmdf as bp
								   ON bc.id_nbcmdf = bp.id_nbcmdf 
								   WHERE bp.pol_num='".$pol_num."'");

if(mysql_num_rows($result) == "0")
{
				$message = "Unable to locate $pol_num (Client Policy Number) in AMDF v3.0";
				unset($_GET);
}
elseif(mysql_num_rows($result) == "1")
{
				header("Location: screens/results.php?searchstring=$pol_num&searchPar=pol_num");
}
else
{
    				$row = mysql_fetch_array($result);
}

[fnbcprog]

I will change those things around. I really appreciate your help.