Slow Query

[snrecords]

I'm new to php mysql.  My previous developer said it was impossible to make a query run faster... I've been trying to read up on improving code for mysql to make queries run faster, but if you provide any help... it would be greatly appreciated.

 

It appears that one page takes so long to load that Googlebot cannot even spider it http://www.buzzhawaii.com/activities/Oahu

 

I will be adding many more rows to this table in the future... what would be my best solution?  Please keep in mind that the site is hosted on some cheap monthly commercial plan.  Do I need to upgrade for the results to display faster or can I just edit the code?

 

function do_search_on_island ($island_name)
	{
		global $vars, $mdb, $category, $island;

	$query = "DROP TABLE IF EXISTS `temp_cat_table`;";
		$rs =& $mdb->Execute($query);

	$query = "CREATE TABLE `temp_cat_table` (
		`category_id` varchar( 32 ) NOT NULL default '',
		`chil_category_name_1` varchar( 128 ) NOT NULL default ''
		) TYPE = MYISAM ;";
		$rs =& $mdb->Execute($query);


	$query = "INSERT INTO temp_cat_table
		SELECT category_id, category_name_1
		FROM category;";
	$rs =& $mdb->Execute($query);


		$query = "SELECT category_child_id AS id
		FROM category_xref
		LEFT JOIN category ON ( category_xref.category_parent_id = category.category_id ) 
		JOIN temp_cat_table ON ( temp_cat_table.category_id = category_xref.category_child_id ) ";


		if($category != "Select")
		{
			$query .= "WHERE category_name_1 = '" . $category . "'";
		}
		/*else
		{
			$query = "SELECT category_child_id AS id
			FROM category_xref
			LEFT JOIN category ON ( category_xref.category_parent_id = category.category_id ) 
			ORDER BY `category_name_1` ASC";
		}*/

	$query .= " ORDER BY `category_name_1` , `chil_category_name_1` ";

	//var_dump($query);

		$rs =& $mdb->Execute($query);
	$ret_value = array();
	while (!$rs->EOF) {
		$par_island = get_parent_category(get_parent_category_as_id($rs->fields[0]));

		if($par_island == $island_name)
			$ret_value[] = array($rs->fields[0], get_parent_category($rs->fields[0]));

		$rs->MoveNext();
	}

	return $ret_value;
	}

	function print_results_for($curr_island)
	{
		global $bg_color, $search_results;

		$ret_value = '';
		$island_img_array = array("Big Island" => "islandybigisland.jpg", 
			"Kauai" => "islandykauai.jpg",
			"Maui" => "islandymaui.jpg",
			"Oahu" => "islandyoahu.jpg");
		$packages = array();
		$packages = do_search_on_island($curr_island);
		$ret_value .= '<table class="taresults" align="center" cellpadding="0" cellspacing="0">';

 	$search_results += count($packages);
 	if (count($packages) > 0)
 	{
 		$ret_value .= '<TR height="4"><TD height="4"></TD></TR>
 									<tr>
                    <td colspan="3">
                        <p class="cisland"><img src="http://www.buzzhawaii.com/images/' . $island_img_array[$curr_island] . '" alt="' . $curr_island . ' Activities"></p>
                    </td>
                	</tr>';

 		$pck_old = array('','');

 		foreach ($packages as $package)
 		{
 		  $pi = get_package_info($package[0]);

 			if ($package[1] != $pck_old[1])
 			{
 				$ret_value .= '	
 				<tr><td class="tourresult" colspan="3">
		                <p class="tourname"><img class="whitearrow" src="http://www.buzzhawaii.com/images/white00_next_11.gif" alt="'. $curr_island .' '. $package[1] . '"><b>' . $package[1] . '</b></p>
		                </td></tr>'; 
 			}

				if ($bg_color == "#EFEFDA")
					$bg_color = "#DFDFB3";
				else
					$bg_color = "#EFEFDA";

 			//$img_src = "images/new021.jpg";
 			$img_src = "http://www.buzzhawaii.com/shop_image/category/" . $pi["category_full_image"];

//	 			$url = "index.php?page=shop/browse_tours&category_id=" . $package[0];

			$dbo = new ps_DB;
			$q = "SELECT category_name_1 FROM category WHERE category_id='".$package[0]."'";
			$dbo->query($q);
			$dbo->next_record();
			$o_url = $dbo->f('category_name_1');
			$o_url = str_replace(" ","_",$o_url);
			//$val = $dbo->num_rows();

			$url = $way.$o_url; 			


 			$lengh_to_cut = 300;
 			if (strlen($pi["category_s_desc_1"]) > $lengh_to_cut)
 			{
 				$info = substr_replace (  $pi["category_s_desc_1"], '', $lengh_to_cut );
 				$info .= " ...";
 			}
 			else
 				$info = $pi["category_s_desc_1"];

			if (get_chipest_pack($package[0]) > 0)
			{
				$price_value = '
					<br><b><i>from</i> </b><SPAN class="bestprice">$' . get_chipest_pack($package[0]) . '</span><br><br>';
			}
			else
				$price_value = '<br>';

 			$ret_value .= '<tr>
                    <td class="tourresultpicture">
                        <A href="http://www.buzzhawaii.com/' . $url . '" ><IMG class="smallpix" src="' . $img_src . '" alt="' . $pi["category_name_1"] . '"></A></td>

		<td class="shorttourdes" bgcolor="' . $bg_color . '">
		<A class="tourtitle1" href="http://www.buzzhawaii.com/' . $url . '"><b><u>'  . $pi["category_name_1"] . '</u></b></A>
		<p>' . $info . '</p>
		</td>
		<td class="shorttourdes1" bgcolor="' . $bg_color . '">
		' . $price_value . '';
                        
        $ret_value  .= '<FORM action="http://www.buzzhawaii.com/' . $url . '"  method="post"><input type="image" src="http://www.buzzhawaii.com/images/select.gif" name="formbutton1" value="SELECT"></FORM></td>
                </tr>';    
 		  
 		  $pck_old = $package;

 		}

 		$ret_value .= '</table>';
    
  }
  
		echo ($ret_value) ;

	}

[~n[EO]n~]

Queries run on server, so if you got yourself a good host then it won't be an issue.

 

My opinion maybe i am wrong

 

[Barand]

try adding key on the temp table that you created

 

$query = "CREATE TABLE `temp_cat_table` (
		`category_id` varchar( 32 ) NOT NULL PRIMARY KEY default '',
		`chil_category_name_1` varchar( 128 ) NOT NULL default ''
		) TYPE = MYISAM ;";