Nested loops and SQL queries; need for speed

I’m having trouble solving a problem with iterative SQL queries (which I need to do away with) and I’m trying to work out an alternative.(Also; unfortunately, AJAX is not really suitable)Given I have the following tables for location data:Country country_id name State state_id country_id name City city_id state_id nameNow, I’m trying to pull all of …

via PHP Website Development » Search Results » ajax:

Nested loops and SQL queries; need for speed

I’m having trouble solving a problem with iterative SQL queries (which I need to do away with) and I’m trying to work out an alternative.
(Also; unfortunately, AJAX is not really suitable)
Given I have the following tables for location data:
Country
country_id
name

State
state_id
country_id
name

City
city_id
state_id
nameNow, I’m trying to pull all of the data, however it’s actually quite tiny (147 cities, split between 64 states, split between 2 countries) however it’s taking forever because I’m iteratively looping:
// this is pseudo-code, but it gets the point across

$countries = getCountries();
foreach($countries as &$country)
$country[‘states’] = $states = getStates($country[‘country_id’]);
foreach($states as &$state)
$state[‘cities’] = getCities($state[‘state_id’]);

}The reason I’m going this way, is because my final result set needs to be in the form:
$countries = array(
array(
‘name’ => ‘country_name’,
‘id’ => ‘country_id’,
‘states’ => array(
array(
‘name’ => ‘state_name’,
‘id’ => ‘state_id’,
‘cities’ => array(
array(
‘name’ => ‘city_name’,
‘id’ => ‘city_id’,
),
// … more cities
),
),
// … more states
),
),
// … more countries
);I can’t seem to wrap my head around a faster approach. What alternatives exist to querying for hierarchical data?

Revised:
$sql = “SELECT
`dbc_country`.`name` as `country_name`,
`dbc_state`.`name` as `state_name`,
`city_id`,
`dbc_city`.`name` as `city_name`,
`latitude`,
`longitude`
FROM
`dbc_city`
INNER JOIN
`dbc_state` ON `dbc_city`.`state_id` = `dbc_state`.`state_id`
INNER JOIN
`dbc_country` ON `dbc_state`.`country_id` = `dbc_country`.`country_id`”;
$locations = array();
foreach($datasource->fetchSet($sql) as $row)
$locations[$row[‘country_name’]][$row[‘state_name’]][] = array(
$row[‘city_id’],
$row[‘city_name’],
$row[‘latitude’],
$row[‘longitude’],
);
(I also removed the id values of states/countries, since they were uselessly taking up space)
…………………………………..

it would be much faster to do joins in the sql
then iterate over the single (larger) result set.
…………………………………..

I would either use one query:
SELECT co.name AS CountryName
, st.name AS StateName
, ci.name AS CityName
FROM Country AS co
LEFT JOIN State AS st
ON st.country_id = co.country_id
LEFT JOIN City AS ci
ON ci.state_id = st.state_id
ORDER BY CountryName
, StateName
, CityNameor three (if you have lots of records and you are worried of sending “United States of America” hundreds of thousands of times over the connection from MySQL to application code):
— **GetCountries**
SELECT co.country_id
, co.name AS CountryName
FROM Country AS co
ORDER BY CountryName

— **GetStates**
SELECT co.country_id
, st.state_id
, st.name AS StateName
FROM Country AS co
JOIN State AS st
ON st.country_id = co.country_id
ORDER BY CountryName
, StateName

— **GetCities**
SELECT co.country_id
, st.state_id
, ci.city_id
, ci.name AS CityName
FROM Country AS co
JOIN State AS st
ON st.country_id = co.country_id
JOIN City AS ci
ON ci.state_id = st.state_id
ORDER BY CountryName
, StateName
, CityName
…………………………………..

The common approach to database design emphasizes doing as much work as possible, with as few queries as possible. Its look right. But quoting this thread title, “Query Efficiency”, that approach doesn’t apply as much to MySQL. FYI, MySQL was designed to handle connecting and disconnecting very efficiently and to respond to small and simple queries very quickly, so as long you immediate freeing memmory on your sequenced queries, i think its okay. Furthermore, if your record growing (into 100000 records for example), then maybe you will think twice to use JOIN statement.
…………………………………..

What if your data looked like this instead?
Table: country
iso_country_code country_name

CA Canada
US United States of America

Table: state
iso_country_code state_abbr state_name

US NE Nebraska
CA NB New Brunswick

Table: city
iso_country_code state_abbr city_name

US NE Lincoln
US NE Arapahoe
CA NB Dalhousie
CA NB MiramichiWould you be able to use the codes and abbreviations instead of the full names?
Even if you can’t, you can get all the necessary rows with a single SELECT statement, then walk the rows to populate your array. (You can do that with ID numbers, too, but with ID numbers, you always have to do the joins. With codes and abbreviations, you can often satisfy your users with just the code or abbreviation.)

For more info: Nested loops and SQL queries; need for speed

PHP Website Development » Search Results » ajax

Nested loops and SQL queries; need for speed

Share this post:

Related Posts

Leave a Comment