Re: Speeding up SELECT statement

Can you post
show create table pages \G

I ask because you'll want an index as follows if not already there
create index page_id_idx1 on pages (mother_id);

also analyze and update your statistics on this table
analyze table pages;
optimize table pages;

Also, you can change your loop logic dramatically

SELECT mother_id, count(*) AS children
FROM pages
GROUP BY mother_id

store this in a hash via $dbh->selectall_hashref()
then for any given page get its mother id which you can get from the 
first query and look it up in this hash
$pages->{$mother_id}->{children}

If you read Tim Bunces Advanced DBI talks (see his CPAN directory)
selectall_hashref() might be slighly slower then a
$sth->bind_columns()
while ($sth->fetch_arrayref()) {
	## build hash
}
$sth->finish()

combination depending on your speed/memory concerns.

Philip


-- 
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe:    http://lists.mysql.com/[email protected]