Speeding up SELECT statement

Hi,

my setup is the following: I request a number of records from the database =
and print them out in an unordered HTML list. For each item of a certain ty=
pe (category), I want to display the number of its "children".

This is what I currently do:

my $query =3D sprintf("SELECT page_id, title, visible, user_id, position, p=
age_type FROM pages WHERE mother_id =3D $id ORDER by position, title LIMIT =
%d,%d", $start-1,$per_page);=20
my $sth =3D $dbh->prepare($query);
$sth->execute();

my $count_query =3D "SELECT page_id FROM pages WHERE mother_id =3D ?";
my $count_handle =3D $dbh->prepare($count_query);

while (my $page =3D $sth->fetchrow_hashref) {
my ($rowcount) =3D $count_handle->execute($page->{page_id}) if $page->{=
page_type} == 0;
[...]
}

I need to speed up the execution of the $count_query, as it slows down the =
display of pages which contain many categories as children.

I tried using SELECT COUNT(*) in the $count_query, but this did not help mu=
ch.

Is there any way to get the results much quicker?

Thanks,

Jan
--=20
I'd never join any club that would have the likes of me as a member. - Grou=
cho Marx

--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=3Dgcdmp-msql-mysql-modules @m.gmane.org

Re: Speeding up SELECT statement

Hi,

Jan Eden wrote on 31.03.2005:

>Hi,
>
>my setup is the following: I request a number of records from the
>database and print them out in an unordered HTML list. For each item
>of a certain type (category), I want to display the number of its
>"children".
>
>This is what I currently do:
>
>my $query =3D sprintf("SELECT page_id, title, visible, user_id,
>position, page_type FROM pages WHERE mother_id =3D $id ORDER by
>position, title LIMIT %d,%d", $start-1,$per_page); my $sth =3D
>$dbh->prepare($query); $sth->execute();
>
>my $count_query =3D "SELECT page_id FROM pages WHERE mother_id =3D ?";
>my $count_handle =3D $dbh->prepare($count_query);
>
>while (my $page =3D $sth->fetchrow_hashref) {
>my ($rowcount) =3D $count_handle->execute($page->{page_id}) if=20
>$page->{page_type} == 0;
>[...]
>}
>
Correction: I used "rows" to get the row count - the version I sent was fau=
lty. Sorry.

>I tried using SELECT COUNT(*) in the $count_query, but this did not
>help much.

Thanks,

Jan
--=20
How many Microsoft engineers does it take to screw in a lightbulb? None. Th=
ey just redefine "dark" as the new standard.

--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=3Dgcdmp-msql-mysql-modules @m.gmane.org

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/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org

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_has=
href 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
--=20
There's no place like ~/

--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=3Dgcdmp-msql-mysql-modules @m.gmane.org