[prev in list] [next in list] [prev in thread] [next in thread] 

List:       mysql
Subject:    Re: searches by groups of..
From:       "Jay J" <3pound () iname ! com>
Date:       1999-05-31 14:21:40
[Download RAW message or body]

This touches on an issue recently talked about on the mod_perl list..

I was using a very high LIMIT as an added precaution (which gets tricky
towards the end of a results set, but that's another story), but simply
doing the counting with Perl.

Essentially: $counter++, <display if within range>, LAST row <if counter ==
per_page>

Under mod_perl, what I stumbled across was the dramatic difference between
the two methods available for fetching rows. (mysql_use_result vs.
mysql_store_result) For DBD::mysql, by default the 'store' method is used.
It basically gobbles the entire SELECT into memory.. which (under mod_perl)
was rearing itself as 20MB servers.

FYI:  my $sth = $dbh->prepare($sql, { "mysql_use_result" => 1});

In my case, I'm always displaying in reverse-date order .. and was able to
accomplish a 'next page' effect while keeping a snapshot of the first query
with a little date_sub() funkiness, and Perl doing the counting. (Therefore
avoiding a 'record_id' list.. for now, anyway)

For what it's worth..

-Jay J



----- Original Message -----
From: Johan Engström <johan.engstrom@hut.fi>
To: Pat Trainor <ptrainor@title14.com>; MyQSL Mailing List
<mysql@lists.mysql.com>
Sent: Monday, May 31, 1999 8:56 AM
Subject: Re: searches by groups of..


> Hi there Pat!
>
> > How should I approach the problem of selecting/lik'ing groups of
> > 10 results with LIMIT? I want to get the effect of what search enines
like
> > altavista do on a search page I'm making that has a huge(well, large)
> > database to search. I want to avoid displaying all matches.
> > Anyone doing this out there?
>
> LIMIT-works quite fine when showing results of relatively simple
> queries with not so heavy loads on the server e.g.
>
> On the first run:
>
> SELECT * FROM thetable LIMIT 0,10;
>
> On the second run:
>
> SELECT * FROM thetable LIMIT 10,10;
>
> ...and so on..
>
> However to minimize the load on the server you should not use this
> technique when dealing with more complex queries or when you want
> to optimize the performance due to high hit-rates. Instead you should
> have an indexed result-table where you store the results of the query
> after the initial call and which you then access using the result-ID
> and LIMIT. Proper use of indexing is crucial when developing
> performance-critical applications with large amounts of data.
>
> Cheers,
>
> Johan
>
>
> ---------------------------------------------------------------------
> Please check "http://www.mysql.com/Manual_chapter/manual_toc.html" before
> posting. To request this thread, e-mail mysql-thread4216@lists.mysql.com
>
> To unsubscribe, send a message to the address shown in the
> List-Unsubscribe header of this message. If you cannot see it,
> e-mail mysql-unsubscribe@lists.mysql.com instead.
>


---------------------------------------------------------------------
Please check "http://www.mysql.com/Manual_chapter/manual_toc.html" before
posting. To request this thread, e-mail mysql-thread4218@lists.mysql.com

To unsubscribe, send a message to the address shown in the
List-Unsubscribe header of this message. If you cannot see it,
e-mail mysql-unsubscribe@lists.mysql.com instead.

[prev in list] [next in list] [prev in thread] [next in thread]