Re: Speeding up SELECT statement

Hi Philip,

Philip M. Gollucci wrote on 31.03.2005:

>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.
>
Works great! I did not even try the bind_columns() method, as selectall_hashref is sufficiently fast in conjunction with the index on mother_id.

I am really a database rookie and greatly appreciate your help. Thank you!

- Jan
-- 
There's no place like ~/

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