StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>You can do it in a single call from php to mysql if you use a stored procedure:</p> <h2>Example calls</h2> <pre><code>mysql> call category_hier(1); +--------+---------------+---------------+----------------------+-------+ | cat_id | category_name | parent_cat_id | parent_category_name | depth | +--------+---------------+---------------+----------------------+-------+ | 1 | Location | NULL | NULL | 0 | | 3 | USA | 1 | Location | 1 | | 4 | Illinois | 3 | USA | 2 | | 5 | Chicago | 3 | USA | 2 | +--------+---------------+---------------+----------------------+-------+ 4 rows in set (0.00 sec) $sql = sprintf("call category_hier(%d)", $id); </code></pre> <p>Hope this helps :)</p> <h2>Full script</h2> <h3>Test table structure:</h3> <pre><code>drop table if exists categories; create table categories ( cat_id smallint unsigned not null auto_increment primary key, name varchar(255) not null, parent_cat_id smallint unsigned null, key (parent_cat_id) ) engine = innodb; </code></pre> <h3>Test data:</h3> <pre><code>insert into categories (name, parent_cat_id) values ('Location',null), ('USA',1), ('Illinois',2), ('Chicago',2), ('Color',null), ('Black',3), ('Red',3); </code></pre> <h3>Procedure:</h3> <pre><code>drop procedure if exists category_hier; delimiter # create procedure category_hier ( in p_cat_id smallint unsigned ) begin declare v_done tinyint unsigned default 0; declare v_depth smallint unsigned default 0; create temporary table hier( parent_cat_id smallint unsigned, cat_id smallint unsigned, depth smallint unsigned default 0 )engine = memory; insert into hier select parent_cat_id, cat_id, v_depth from categories where cat_id = p_cat_id; /* http://dev.mysql.com/doc/refman/5.0/en/temporary-table-problems.html */ create temporary table tmp engine=memory select * from hier; while not v_done do if exists( select 1 from categories p inner join hier on p.parent_cat_id = hier.cat_id and hier.depth = v_depth) then insert into hier select p.parent_cat_id, p.cat_id, v_depth + 1 from categories p inner join tmp on p.parent_cat_id = tmp.cat_id and tmp.depth = v_depth; set v_depth = v_depth + 1; truncate table tmp; insert into tmp select * from hier where depth = v_depth; else set v_done = 1; end if; end while; select p.cat_id, p.name as category_name, b.cat_id as parent_cat_id, b.name as parent_category_name, hier.depth from hier inner join categories p on hier.cat_id = p.cat_id left outer join categories b on hier.parent_cat_id = b.cat_id order by hier.depth, hier.cat_id; drop temporary table if exists hier; drop temporary table if exists tmp; end # </code></pre> <h3>Test runs:</h3> <pre><code>delimiter ; call category_hier(1); call category_hier(2); </code></pre> <h2>Some performance testing using Yahoo geoplanet places data</h2> <pre><code>drop table if exists geoplanet_places; create table geoplanet_places ( woe_id int unsigned not null, iso_code varchar(3) not null, name varchar(255) not null, lang varchar(8) not null, place_type varchar(32) not null, parent_woe_id int unsigned not null, primary key (woe_id), key (parent_woe_id) ) engine=innodb; mysql> select count(*) from geoplanet_places; +----------+ | count(*) | +----------+ | 5653967 | +----------+ </code></pre> <p>so that's 5.6 million rows (places) in the table let's see how the adjacency list implementation/stored procedure called from php handles that.</p> <pre><code> 1 records fetched with max depth 0 in 0.001921 secs 250 records fetched with max depth 1 in 0.004883 secs 515 records fetched with max depth 1 in 0.006552 secs 822 records fetched with max depth 1 in 0.009568 secs 918 records fetched with max depth 1 in 0.009689 secs 1346 records fetched with max depth 1 in 0.040453 secs 5901 records fetched with max depth 2 in 0.219246 secs 6817 records fetched with max depth 1 in 0.152841 secs 8621 records fetched with max depth 3 in 0.096665 secs 18098 records fetched with max depth 3 in 0.580223 secs 238007 records fetched with max depth 4 in 2.003213 secs </code></pre> <p>Overall i'm pretty pleased with those cold runtimes as I wouldn't even begin to consider returning tens of thousands of rows of data to my front end but would rather build the tree dynamically fetching only several levels per call. Oh and just incase you were thinking innodb is slower than myisam - the myisam implementation I tested was twice as slow in all counts.</p> <p>More stuff here : <a href="http://pastie.org/1672733" rel="noreferrer">http://pastie.org/1672733</a></p> <p>Hope this helps :)</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload