Path: csiph.com!fu-berlin.de!uni-berlin.de!individual.net!not-for-mail
From: "J.O. Aho" <user@example.net>
Newsgroups: comp.lang.php
Subject: Re: Php/Mysql paginate results without resubmitting query
Date: Thu, 19 Nov 2020 22:54:31 +0100
Lines: 35
Message-ID: <i1o7snFbhv4U1@mid.individual.net>
References: <rp67bt$up2$1@gioia.aioe.org>
Mime-Version: 1.0
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 8bit
X-Trace: individual.net oE3Zs2cZuaFJM+HGJ4GJGAc4GoY5pWh6seSLb4F/jYc3SHVI5E
Cancel-Lock: sha1:6V7ulYy++ER6SQpI5qeTehq8yNY=
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:68.0) Gecko/20100101 Thunderbird/68.12.0
In-Reply-To: <rp67bt$up2$1@gioia.aioe.org>
Content-Language: en-US-large
Xref: csiph.com comp.lang.php:18416

On 19/11/2020 17.43, cb wrote:
> Please be patients !
> I'm doing a big (sort of) select:
> SELECT column1,column2 FROM table ORDER BY column1 LIMIT $count OFFSET 
> $offset;
> (Without the LIMIT the select fails for exceeding time limit or whatelse.)
> The user is then allowed to "page down" for getting next $count rows.
> It works, BUT the query is re-submitted scanning again the whole DB.
> 
> Is there a way to store the RESULTS of the original query, and then 
> "paginate" into it (into the stored results) not into the DB?

You would need memcached or other cache system, you check if your data 
structure is stored in the cache system, if not then you fetch the data 
and process it to the format you want to use and store it to the cache.

Keep in mind that you will have an execution timeout when you try to 
fetch all the data, so it wouldn't help you anything to use a cache system.


> Googling around I've found (and I'm doing it!)
>     $rc = mysqli_query($db, $qlib);
>     while ($row = mysqli_fetch_array($rc)) {
>        array_push($righe, $row);
>     }
> BUT if $qlib has no LIMIT it abend for exceeding limits.

For it's faster to work on a subset of the data than the whole data.

As Jerry already pointed out, you should look at using indexes, as it 
will improve your sort speed.

-- 

  //Aho