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
name
Now, 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
, CityName
or 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 Miramichi
Would 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.)
精彩评论