· 8 years ago · Jan 27, 2018, 07:46 AM
1##PHP
2
3<?php
4ob_start();
5
6try
7{
8 // idx 0 = 238K rows ... idx 5 = 1346 rows ... idx 10 = 1 row
9 $woe_idx = 0; // 0 to 10
10 $woe_ids = array(44418,12695819,12695816,20094361,12695828,41093,31307,17043,37152,20094326,28142893);
11
12 $db = new mysqli("localhost", "geo_dbo", "pass", "geo_db", 3306);
13
14 if ($db->connect_errno)
15 throw new exception(sprintf("Could not connect: %s", $db->connect_error));
16
17 //would recommend limiting the number of rows returned to a sensible page limit !
18 $sqlCmd = sprintf("call geoplanet_places_hier(%d);", $woe_ids[$woe_idx]);
19
20 $startTime = microtime(true);
21 $result = $db->query($sqlCmd);
22
23 if(!$result) throw new exception(sprintf("Invalid query : %s", $sqlCmd));
24
25 if($result->num_rows <= 0){
26 echo "no geoplanet places found !";
27 }
28 else{
29
30 $row = $result->fetch_assoc();
31
32 echo sprintf("<b>%d records fetched with max depth %d in %s secs</b><br/>",
33 $row["counter"], $row["max_depth"], number_format(microtime(true) - $startTime, 6, ".", ""));
34
35 echo "<table border='1'>
36 <tr><th>woe_id</th><th>name</th><th>place_type</th><th>parent_woe_id</th>
37 <th>parent_name</th><th>parent_place_type</th><th>depth</th></tr>";
38 $counter = 0;
39 do
40 {
41 $counter++;
42 echo sprintf("<tr><td>%s</td><td>%s</td><td>%s</td><td>%s</td>
43 <td>%s</td><td>%s</td><td>%s</td></tr>",
44 $row["woe_id"], $row["name"], $row["place_type"], $row["parent_woe_id"],
45 $row["parent_name"], $row["parent_place_type"],$row["depth"]);
46 }
47 while($counter < 100 && $row = $result->fetch_assoc());
48 echo "</table>";
49 }
50 $result->close();
51}
52catch(exception $ex)
53{
54 ob_clean();
55 echo sprintf("zomg borked - %s", $ex->getMessage());
56}
57
58if(!$db->connect_errno) $db->close();
59ob_end_flush();
60?>
61
62
63
64## SQL
65
66drop table if exists geoplanet_places;
67create table geoplanet_places
68(
69woe_id int unsigned not null,
70iso_code varchar(3) not null,
71name varchar(255) not null,
72lang varchar(8) not null,
73place_type varchar(32) not null,
74parent_woe_id int unsigned not null,
75primary key (woe_id),
76key (parent_woe_id)
77)
78engine=innodb;
79
80
81drop procedure if exists geoplanet_places_hier;
82
83delimiter #
84
85create procedure geoplanet_places_hier
86(
87in p_woe_id int unsigned
88)
89begin
90
91declare v_done tinyint unsigned default(0);
92declare v_dpth smallint unsigned default(0);
93declare v_counter int unsigned default(0);
94
95create temporary table hier(
96 parent_woe_id int unsigned,
97 woe_id int unsigned,
98 depth smallint unsigned
99)engine=memory;
100
101create temporary table tmp engine=memory select * from hier;
102
103insert into hier select parent_woe_id, woe_id, v_dpth from geoplanet_places where woe_id = p_woe_id;
104insert into tmp select * from hier;
105
106while not v_done do
107
108 if exists( select 1 from geoplanet_places gp
109 inner join tmp on gp.parent_woe_id = tmp.woe_id and tmp.depth = v_dpth) then
110
111 insert into hier select gp.parent_woe_id, gp.woe_id, v_dpth + 1
112 from geoplanet_places gp inner join tmp on gp.parent_woe_id = tmp.woe_id and tmp.depth = v_dpth;
113
114 set v_dpth = v_dpth + 1;
115
116 truncate table tmp;
117 insert into tmp select * from hier where depth = v_dpth;
118
119 select count(*) + v_counter into v_counter from tmp;
120 else
121 set v_done = 1;
122 end if;
123end while;
124
125select
126 v_counter as counter,
127 v_dpth as max_depth,
128 gp.woe_id,
129 gp.name,
130 gp.place_type,
131 p.woe_id as parent_woe_id,
132 p.name as parent_name,
133 p.place_type as parent_place_type,
134 hier.depth
135from
136 hier
137inner join geoplanet_places gp on hier.woe_id = gp.woe_id
138inner join geoplanet_places p on hier.parent_woe_id = p.woe_id
139order by
140 hier.depth;
141
142drop temporary table if exists hier;
143drop temporary table if exists tmp;
144
145end #
146
147delimiter ;
148
149## IMPORT
150
151truncate table geoplanet_places;
152
153start transaction;
154
155load data infile 'D:\\f00\\My Dev\\MySQL\\VLDB\\geoplanet\\import\\places.dat'
156into table geoplanet_places
157fields terminated by '|' optionally enclosed by '"'
158lines terminated by '\r\n'
159(
160woe_id,
161iso_code,
162name,
163lang,
164place_type,
165parent_woe_id
166)
167set
168name = nullif(name,'');
169
170commit;
171
172##TESTING
173
174select * from geoplanet_places where parent_woe_id = 44418 -- London
175
176call geoplanet_places_hier(44418); -- 238K rows
177call geoplanet_places_hier(12695819);
178call geoplanet_places_hier(12695816);
179call geoplanet_places_hier(20094361);
180call geoplanet_places_hier(12695828);
181call geoplanet_places_hier(41093); -- 1346 rows
182call geoplanet_places_hier(31307);
183call geoplanet_places_hier(17043);
184call geoplanet_places_hier(37152);
185call geoplanet_places_hier(20094326);
186call geoplanet_places_hier(28142893); -- 1 row
187
1881 records fetched with max depth 0 in 0.001921 secs
189250 records fetched with max depth 1 in 0.004883 secs
190515 records fetched with max depth 1 in 0.006552 secs
191822 records fetched with max depth 1 in 0.009568 secs
192918 records fetched with max depth 1 in 0.009689 secs
1931346 records fetched with max depth 1 in 0.040453 secs
1945901 records fetched with max depth 2 in 0.219246 secs
1956817 records fetched with max depth 1 in 0.152841 secs
1968621 records fetched with max depth 3 in 0.096665 secs
19718098 records fetched with max depth 3 in 0.580223 secs
198238007 records fetched with max depth 4 in 2.003213 secs