"MySQL server has gone away" when executing large query

[jay.barnes]

MySQL server version: 5.0.51a (on an IIS server running Plesk control panel)

 

I'm running a query to try and locate shipments available within a specific radius of a zip code.  When I was just using USA (approx 70,000 zips  on the table), the query ran fine, no matter what radius I specified (anywhere between 25, 50, 100, 150, or 200 miles).  Now that I updated my code to include Canada (has its own table of approx 830,000 zips), whenever I try to execute a radius search that happens to include Canadian zip codes, any radius search larger than 50 miles returns an error that "The MySQL server has gone away".  It sounds to me that the server is choking on the query (not that I can blame it, with the number of zip codes Canada seems to have).

 

I have tried mitigating this by going into my.cfg (found in "c:\Parllels\Plesk\MySQL\Data") and changing the "max_allowed_packet" setting from 1M to 1024M (restarting the server after making the changes, just to make sure it took), but that didn't seem to have any effect.

 

I was also thinking of retooling the query to only search for city names and state names instead of zip codes (as there are fewer cities out there than zip codes), but I'm not sure how to phrase the zip code query to output cities linked to states in a manner that I could use in the main query (for example, searching for loads within 25 miles of Springfield, VA, doesn't return loads in Springfield, IL, simply because it has a city named "Springfield" in it).  I suspect it would have something to do with "GROUP BY", and nesting the city in the  state criteria using the "GROUP BY" modifier, but I have yet to get satisfactory results with that.

 

Here's an example of the main load search query below, which may help illustrate things a bit (I omitted all the zip codes in the query, for readability's sake)

 

SELECT Broker_JobNum, Username, Entry_UpdateDateTime, Broker_CompanyName, Shipment_Type, Required_Equipment,  Load_Length, Load_Weight, Available_Loads.Origin_City, Origin_State, Origin_Zip, PU_Date, Dest_City, Dest_State, Dest_Zip, Delivery_Date, Frequency 
FROM Available_Loads"
WHERE Required_Equipment IN ('V')
AND Shipment_Type LIKE 'TL'
AND Action != 'D'
AND Username != 'demo' 
AND Status != 'inactive'
AND Origin_Zip != '00000'
AND Origin_Zip != '99999'
AND Origin_Zip IN ('$AWHOLELOTTAZIPCODES')
ORDER BY ".$_GET['OrderBy']

 

Are there any suggestions anyone may have which may help me either lighten the load on the server by restructuring the query (may be unlikely), or elevating the MySQL server's load tolerance in a way that I just haven't figured out yet?

 

Thanks, and please let me know if you need me to supply any more pertinent info!

 

-Jay

[mapleleaf]

max_exection_time is probably 30 seconds. You will need to increase it though that isn't practical as most people won't wait so long to find out.

How did you end up with 70,000 US zip codes? My database of zips has only 43,000 or thereabouts.

I would suggest not allowing searches of more than 50 miles or find a way of caching as checking against every zip is a lot of queries. With 800,000 zips it will be too much for most to wait for even if you get the execution time sorted.

 

 

[jay.barnes]

max_exection_time is probably 30 seconds. You will need to increase it though that isn't practical as most people won't wait so long to find out.

How did you end up with 70,000 US zip codes? My database of zips has only 43,000 or thereabouts.

I would suggest not allowing searches of more than 50 miles or find a way of caching as checking against every zip is a lot of queries. With 800,000 zips it will be too much for most to wait for even if you get the execution time sorted.

 

Yeah, you were more on point - my USA zip table has closer to 43000 records.  I may have to see what other options I have at my disposal regarding Canada and their micro-managed zip code system.  :-p

 

Thanks for the advice!

[jay.barnes]

Still trying to find a way to address this issue....

 

Recap:  I have a table, which lists several loads, and their pickup origins, structured as such (irrelevant fields removed):

 

Table: LOADS
------------------------------------------------------
| Load_Num | Origin_City | Origin_State | Origin_Zip |
|----------|-------------|--------------|------------| 
|     1    |    Miami    |      FL      |    33101   |
|     2    |   Toronto   |      ON      |   M4B 1V4  |
|     3    |    Boston   |      MA      |    02108   |
|     4    |   Vancouver |      BC      |   V5T 1Y9  |
------------------------------------------------------

 

My users can type a zip code, and a radius of up to 200 miles into two fields, and execute a search which:

 

1. Finds the lat and lon of the entered zip code, searching two tables on my DB (Can ZIPs, and USA ZIPs)

2. Runs the appropriate equation to find all zip codes that are within that radius

3. Converts the array of zip codes to a string, with values separated by a comma.

3. Executes a query of the "Loads" table, searching for all loads that originate in any one of those zip codes.

 

An example query would be

 "SELECT* FROM Available_Loads WHERE Origin_Zip IN (a,whole,lotta,zip,codes') ORDER BY ".$_GET['OrderBy']"

 

When I was only conducting searches in the US, I wouldn't have much of a problem getting results, as I'd only be searching through, say < 1000 zips total.  However, once I introduced Canada to the mix (yeah, blame Canada!), the size of my queries grew exponentially, due to their ZIP code system (for example, in my table of Canadian ZIPs, Toronto, ON, has 13,959 ZIP codes in that one city *alone*).  This is causing my MySQL server to choke on queries that involve Canadian ZIPs, and not complete the script.

 

I was wondering if there was any way I could tweak my search code, so that, instead of searching the "Loads" board for all loads that contain all those ZIP codes, I could execute a search that would just match against the City/State combinations.  (forgive my lack of proper syntax, here, but I guess the query would look something like

  "SELECT* FROM Available_Loads WHERE Origin_City, Origin_State IN ('Springfield, VA','Arlington, VA') ORDER BY ".$_GET['OrderBy']"

 

I think that would make it more efficient, but I'm not sure how I'd go about making sure that the cities and states "stick together" during the execution of a search (For example, if I'm doing a search for all loads within 500 miles of Springfield, IL, and some locations in VA show up, the query doesn't return "Springfield, VA" as a possible result, simply because "Springfield" happened to be one of the matching cities, and "VA" happened to be among the matching states.)

 

Below is the code that runs the data transformations from user input to the "Loads" board query execution.  Can anyone offer up some suggestions on how I might be able match against city/state combinations as opposed to zip codes?

 

Thanks for any suggestions you may have!

 

-Jay

 

// retrieve the zip code and distance values from the form
$zip_code = $_POST['Search_Origin_Radius_Point'];
$miles = $_POST['Search_Origin_Radius'];

$query_search_origin_zip = "SELECT latitude, longitude FROM zip_codes_usa WHERE zip_code = '$zip_code' UNION SELECT latitude, longitude FROM zip_codes_can WHERE zip_code = '$zip_code' LIMIT 0,1";
$search_origin_zip = mysql_query($query_search_origin_zip, $Connection) or die("Unable to execute query $query_search_origin_zip: ".mysql_error());
$row_search_origin_zip = mysql_fetch_assoc($search_origin_zip);
$totalRows_search_origin_zip = mysql_num_rows($search_origin_zip);
$array_search_origin_zip = mysql_fetch_array($search_origin_zip);

if ($totalRows_search_origin_zip  == 0)
{
    // the zip code does not exists
    echo "The origin zip code was not found in the database";
}
else
{
// the zip code was found so process the data
$lat = $row_search_origin_zip['latitude'];
$lon = $row_search_origin_zip['longitude'];

// get the min/max latitudes and longitudes for the radius search
$lat_range = $miles / 69.172;
$lon_range = abs($miles / (cos($lon) * 69.172));

$min_lat = $lat - $lat_range; 
$max_lat = $lat + $lat_range;
$min_lon = $lon - $lon_range;
$max_lon = $lon + $lon_range;

// apply the min/max coordinates to the sql query to only select those items within range
$query_search_origin_range = "SELECT DISTINCT zip_code, city_name, state_abbrev, latitude, longitude FROM zip_codes_usa WHERE ((latitude >= $min_lat AND latitude <= $max_lat) AND (longitude >= $min_lon AND longitude <= $max_lon)) AND `longitude` != '0.000000' AND `latitude` != '0.000000' UNION SELECT DISTINCT zip_code, city_name, state_abbrev, latitude, longitude FROM zip_codes_can WHERE ((latitude >= $min_lat AND latitude <= $max_lat) AND (longitude >= $min_lon AND longitude <= $max_lon)) AND `longitude` != '0.000000' AND `latitude` != '0.000000' ORDER BY zip_code DESC";
$search_origin_range = mysql_query($query_search_origin_range, $Connection) or die(mysql_error());
$row_search_origin_range = mysql_fetch_assoc($search_origin_range);
$totalRows_search_origin_range = mysql_num_rows($search_origin_range);

while ($array_search_origin_range = mysql_fetch_array($search_origin_range))
{
	// check the distance to make sure it's less than the entered radius
	$origin_dist = distance($lat, $lon, $array_search_origin_range["latitude"], $array_search_origin_range["longitude"]);
	if ($origin_dist <= $miles)
	{
		// the zip code is within the requested radius so process the results
		$search_origin_city[] = $array_search_origin_range["city_name"];
		$search_origin_zip_code[] = $array_search_origin_range["zip_code"];
		$origin_distance[$array_search_origin_range["zip_code"]] = $origin_dist;
	}
}
}

//compile results into an array
$search_origin_zip_code = array_values($search_origin_zip_code);

//remove empty array values, if any
foreach($search_origin_zip_code as $key => $value)
{
  if($value == "") 
         {
	unset($search_origin_zip_code[$key]);
  }
}

//remove duplicate values, if any	
$search_origin_zip_code = array_unique($search_origin_zip_code);	

//convert array values back into a string
$search_origin_zip_code = implode('\', \'',$search_origin_zip_code);

//add array string to session data, so search can be performed again and/or revised, if needed
if (!($_SESSION['search']))
{
$_SESSION['search'] = "locked";
$_SESSION['search_origin_radius'] = $_POST['Search_Origin_Radius'];
}

//execute main board query
$query_AvailableLoads = "SELECT * FROM Loads WHERE Origin_Zip IN ('$_SESSION[search_origin_zip_code]') ORDER BY ".$_GET['OrderBy'];
$AvailableLoads = mysql_query($query_AvailableLoads, $Connection) or die("Unable to execute main query".mysql_error());
$row_AvailableLoads = mysql_fetch_assoc($AvailableLoads);
$totalRows_AvailableLoads = mysql_num_rows($AvailableLoads);