Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.lang.php > #18413 > unrolled thread

Php/Mysql paginate results without resubmitting query

Started bycb <cb@ppt.io.it>
First post2020-11-19 17:43 +0100
Last post2020-11-20 17:42 +0100
Articles 11 — 4 participants

Back to article view | Back to comp.lang.php

Contents

  Php/Mysql paginate results without resubmitting query cb <cb@ppt.io.it> - 2020-11-19 17:43 +0100
    Re: Php/Mysql paginate results without resubmitting query Jerry Stuckle <jstucklex@attglobal.net> - 2020-11-19 15:05 -0500
      Re: Php/Mysql paginate results without resubmitting query cb <cb@ppt.io.it> - 2020-11-21 18:03 +0100
    Re: Php/Mysql paginate results without resubmitting query "J.O. Aho" <user@example.net> - 2020-11-19 22:54 +0100
      Re: Php/Mysql paginate results without resubmitting query cb <cb@ppt.io.it> - 2020-11-20 08:36 +0100
        Re: Php/Mysql paginate results without resubmitting query cb <cb@ppt.io.it> - 2020-11-20 08:45 +0100
          Re: Php/Mysql paginate results without resubmitting query cb <cb@ppt.io.it> - 2020-11-20 08:53 +0100
        Re: Php/Mysql paginate results without resubmitting query "J.O. Aho" <user@example.net> - 2020-11-20 10:15 +0100
          Re: Php/Mysql paginate results without resubmitting query cb <cb@ppt.io.it> - 2020-11-20 11:30 +0100
          Re: Php/Mysql paginate results without resubmitting query Jerry Stuckle <jstucklex@attglobal.net> - 2020-11-20 11:05 -0500
    Re: Php/Mysql paginate results without resubmitting query Arno Welzel <usenet@arnowelzel.de> - 2020-11-20 17:42 +0100

#18413 — Php/Mysql paginate results without resubmitting query

Fromcb <cb@ppt.io.it>
Date2020-11-19 17:43 +0100
SubjectPhp/Mysql paginate results without resubmitting query
Message-ID<rp67bt$up2$1@gioia.aioe.org>
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?

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.

THANK YOU !

[toc] | [next] | [standalone]

#18415

FromJerry Stuckle <jstucklex@attglobal.net>
Date2020-11-19 15:05 -0500
Message-ID<rp6j6v$nff$1@jstuckle.eternal-september.org>
In reply to#18413
On 11/19/2020 11:43 AM, 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?
> 
> 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.
> 
> THANK YOU !

How big is your database (and which RDBMS are you using)?  What indexes 
do you have on it?  Exactly what is your query?  What is your 
max_execution_time?

Rather than resubmitting the result, it would be better if you can 
optimize your query so it doesn't time out.  But this will only work if 
the script doesn't terminate (i.e. end of the web page) because stored 
results are discarded unless they are saved in your $_SESSION (not 
recommended for big data).

Otherwise your only option is to query the database again, updating your 
$offset every time.

-- 
==================
Remove the "x" from my email address
Jerry Stuckle
jstucklex@attglobal.net
==================

[toc] | [prev] | [next] | [standalone]

#18427

Fromcb <cb@ppt.io.it>
Date2020-11-21 18:03 +0100
Message-ID<rpbh9j$1282$1@gioia.aioe.org>
In reply to#18415
I have to apologize for the disturb/noise caused :-(
Setting the right key (*), and removing Strumento* (that I actually 
didn't need) from the ORDER solved all my troubles.
Thanks for all the help you gave me: it has been very helpful and I 
really appreciate it.

(*) but sh## I was sure to have already created it .... I was wrong :-(

[toc] | [prev] | [next] | [standalone]

#18416

From"J.O. Aho" <user@example.net>
Date2020-11-19 22:54 +0100
Message-ID<i1o7snFbhv4U1@mid.individual.net>
In reply to#18413
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

[toc] | [prev] | [next] | [standalone]

#18417

Fromcb <cb@ppt.io.it>
Date2020-11-20 08:36 +0100
Message-ID<rp7rl7$n54$1@gioia.aioe.org>
In reply to#18416
Jerry & J.O. thanks for your attention !
 >> How big is your database
    400.000 rows
 >> table definition:
  MYSQL
  "CREATE TABLE IF NOT EXISTS `Tablescore` (  \n"
               + "   `Progr` INT AUTO_INCREMENT ,  \n"
               + "   `Autore` varchar(512) NOT NULL,  \n"
               + "   `Lavoro` varchar(512) DEFAULT NULL,  \n"
               + "   `Link` varchar(512) DEFAULT NULL,  \n"
               + "   `NumFil` varchar(160) DEFAULT NULL,  \n"
               + "   `DateSubm` varchar(10) DEFAULT NULL,  \n"
               + "   `PlaceHolder` varchar(30) DEFAULT NULL,\n"
               + "   `StrumentoGenerico` varchar(130) DEFAULT NULL,\n"
               + "   `StrumentoSpecifico` varchar(130) DEFAULT NULL\n"
               + "   , PRIMARY KEY (Progr)) \n"
               + "ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=0 ";
 >> query:
   SELECT * from Tablescore ORDER BY Autore , Lavoro, StrumentoGenerico, 
StrumentoSpecifico
 >> max_execution_time:
  N/A I'm running on an hosted system, so time_limit(anything) it's non 
honored.
  and that's reasonable !

On another (italian) newsgroup I got the suggestion to use a temp table.
I'll try it during the week-end.

[toc] | [prev] | [next] | [standalone]

#18418

Fromcb <cb@ppt.io.it>
Date2020-11-20 08:45 +0100
Message-ID<rp7s6v$toc$1@gioia.aioe.org>
In reply to#18417
addendum
 >> indexes:
describe Tablescore;
+--------------------+--------------+------+-----+---------+----------------+
| Field              | Type         | Null | Key | Default | Extra 
    |
+--------------------+--------------+------+-----+---------+----------------+
| Progr              | int(11)      | NO   | PRI | NULL    | 
auto_increment |
| Autore             | varchar(512) | NO   | MUL | NULL    | 
    |
| Lavoro             | varchar(512) | YES  | MUL | NULL    | 
    |
| Link               | varchar(512) | YES  |     | NULL    | 
    |
| NumFil             | varchar(160) | YES  |     | NULL    | 
    |
| DateSubm           | varchar(10)  | YES  |     | NULL    | 
    |
| PlaceHolder        | varchar(30)  | YES  |     | NULL    | 
    |
| StrumentoGenerico  | varchar(130) | YES  | MUL | NULL    | 
    |
| StrumentoSpecifico | varchar(130) | YES  | MUL | NULL    | 
    |
+--------------------+--------------+------+-----+---------+----------------+
9 rows in set (0.00 sec)

On 11/20/20 8:36 AM, cb wrote:
> Jerry & J.O. thanks for your attention !
>  >> How big is your database
>     400.000 rows
>  >> table definition:
>   MYSQL
>   "CREATE TABLE IF NOT EXISTS `Tablescore` (  \n"
>                + "   `Progr` INT AUTO_INCREMENT ,  \n"
>                + "   `Autore` varchar(512) NOT NULL,  \n"
>                + "   `Lavoro` varchar(512) DEFAULT NULL,  \n"
>                + "   `Link` varchar(512) DEFAULT NULL,  \n"
>                + "   `NumFil` varchar(160) DEFAULT NULL,  \n"
>                + "   `DateSubm` varchar(10) DEFAULT NULL,  \n"
>                + "   `PlaceHolder` varchar(30) DEFAULT NULL,\n"
>                + "   `StrumentoGenerico` varchar(130) DEFAULT NULL,\n"
>                + "   `StrumentoSpecifico` varchar(130) DEFAULT NULL\n"
>                + "   , PRIMARY KEY (Progr)) \n"
>                + "ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=0 ";
>  >> query:
>    SELECT * from Tablescore ORDER BY Autore , Lavoro, StrumentoGenerico, 
> StrumentoSpecifico
>  >> max_execution_time:
>   N/A I'm running on an hosted system, so time_limit(anything) it's non 
> honored.
>   and that's reasonable !
> 
> On another (italian) newsgroup I got the suggestion to use a temp table.
> I'll try it during the week-end.

[toc] | [prev] | [next] | [standalone]

#18419

Fromcb <cb@ppt.io.it>
Date2020-11-20 08:53 +0100
Message-ID<rp7sld$13k8$1@gioia.aioe.org>
In reply to#18418
another addendum:
explain SELECT * from Tablescore ORDER BY Autore , Lavoro, 
StrumentoGenerico, StrumentoSpecifico ;
+------+-------------+------------+------+---------------+------+---------+------+--------+----------------+
| id   | select_type | table      | type | possible_keys | key  | 
key_len | ref  | rows   | Extra          |
+------+-------------+------------+------+---------------+------+---------+------+--------+----------------+
|    1 | SIMPLE      | Tablescore | ALL  | NULL          | NULL | NULL 
  | NULL | 381853 | Using filesort |
+------+-------------+------------+------+---------------+------+---------+------+--------+----------------+
1 row in set (0.00 sec)
it looks like I have to investigate why no KEY is used
:-(
On 11/20/20 8:45 AM, cb wrote:
> addendum
>  >> indexes:
> describe Tablescore;
> +--------------------+--------------+------+-----+---------+----------------+ 
> 
> | Field              | Type         | Null | Key | Default | Extra    |
> +--------------------+--------------+------+-----+---------+----------------+ 
> 
> | Progr              | int(11)      | NO   | PRI | NULL    | 
> auto_increment |
> | Autore             | varchar(512) | NO   | MUL | NULL    |    |
> | Lavoro             | varchar(512) | YES  | MUL | NULL    |    |
> | Link               | varchar(512) | YES  |     | NULL    |    |
> | NumFil             | varchar(160) | YES  |     | NULL    |    |
> | DateSubm           | varchar(10)  | YES  |     | NULL    |    |
> | PlaceHolder        | varchar(30)  | YES  |     | NULL    |    |
> | StrumentoGenerico  | varchar(130) | YES  | MUL | NULL    |    |
> | StrumentoSpecifico | varchar(130) | YES  | MUL | NULL    |    |
> +--------------------+--------------+------+-----+---------+----------------+ 
> 
> 9 rows in set (0.00 sec)
> 
> On 11/20/20 8:36 AM, cb wrote:
>> Jerry & J.O. thanks for your attention !
>>  >> How big is your database
>>     400.000 rows
>>  >> table definition:
>>   MYSQL
>>   "CREATE TABLE IF NOT EXISTS `Tablescore` (  \n"
>>                + "   `Progr` INT AUTO_INCREMENT ,  \n"
>>                + "   `Autore` varchar(512) NOT NULL,  \n"
>>                + "   `Lavoro` varchar(512) DEFAULT NULL,  \n"
>>                + "   `Link` varchar(512) DEFAULT NULL,  \n"
>>                + "   `NumFil` varchar(160) DEFAULT NULL,  \n"
>>                + "   `DateSubm` varchar(10) DEFAULT NULL,  \n"
>>                + "   `PlaceHolder` varchar(30) DEFAULT NULL,\n"
>>                + "   `StrumentoGenerico` varchar(130) DEFAULT NULL,\n"
>>                + "   `StrumentoSpecifico` varchar(130) DEFAULT NULL\n"
>>                + "   , PRIMARY KEY (Progr)) \n"
>>                + "ENGINE=InnoDB  DEFAULT CHARSET=latin1 
>> AUTO_INCREMENT=0 ";
>>  >> query:
>>    SELECT * from Tablescore ORDER BY Autore , Lavoro, 
>> StrumentoGenerico, StrumentoSpecifico
>>  >> max_execution_time:
>>   N/A I'm running on an hosted system, so time_limit(anything) it's 
>> non honored.
>>   and that's reasonable !
>>
>> On another (italian) newsgroup I got the suggestion to use a temp table.
>> I'll try it during the week-end.
>

[toc] | [prev] | [next] | [standalone]

#18420

From"J.O. Aho" <user@example.net>
Date2020-11-20 10:15 +0100
Message-ID<i1pfq7Fim54U1@mid.individual.net>
In reply to#18417
On 20/11/2020 08.36, cb wrote:
> Jerry & J.O. thanks for your attention !
>  >> How big is your database
>     400.000 rows
>  >> table definition:
>   MYSQL
>   "CREATE TABLE IF NOT EXISTS `Tablescore` (  \n"
>                + "   `Progr` INT AUTO_INCREMENT ,  \n"
>                + "   `Autore` varchar(512) NOT NULL,  \n"
>                + "   `Lavoro` varchar(512) DEFAULT NULL,  \n"
>                + "   `Link` varchar(512) DEFAULT NULL,  \n"
>                + "   `NumFil` varchar(160) DEFAULT NULL,  \n"
>                + "   `DateSubm` varchar(10) DEFAULT NULL,  \n"
>                + "   `PlaceHolder` varchar(30) DEFAULT NULL,\n"
>                + "   `StrumentoGenerico` varchar(130) DEFAULT NULL,\n"
>                + "   `StrumentoSpecifico` varchar(130) DEFAULT NULL\n"
>                + "   , PRIMARY KEY (Progr)) \n"
>                + "ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=0 ";

I guess the multi key index is Autore , Lavoro, StrumentoGenerico, 
StrumentoSpecifico and I think that in InnoDB you have a key limit of 
767 bytes, so as I can recall your key size would be

512*2 + 512*2 + 130*1 + 130*1 = 2308

the two first one should automatically be made to double bytes as they 
are larger than 255 chars. Lets see if Jerry agrees with that conclusion.


> On another (italian) newsgroup I got the suggestion to use a temp table.
> I'll try it during the week-end.

I doubt that would make much difference as you ain't joining things and 
the temp table will be removed when the mysql session is terminated, so 
on your second connection you can't use the data.

One alternative would be to do a select on the whole table, sort it in 
php and store the result in a cache, how effective this is depends on 
how often you write to the table as the cache needs to invalidated when 
you do insert/update/delete.

Another alternative is to use a faster database server, like aws aurora, 
but sure it comes with an extra cost.

-- 

  //Aho

[toc] | [prev] | [next] | [standalone]

#18421

Fromcb <cb@ppt.io.it>
Date2020-11-20 11:30 +0100
Message-ID<rp85t0$1dv6$1@gioia.aioe.org>
In reply to#18420
On 11/20/20 10:15 AM, J.O. Aho wrote:
> I guess the multi key index is Autore , Lavoro, StrumentoGenerico, 
> StrumentoSpecifico and I think that in InnoDB you have a key limit of 
> 767 bytes, so as I can recall your key size would be
thank you for your attention/patience !
actually I've already done the multi-key on the fields you 
suggested:that changed *nothing*
Now I understand why :-(
Now I have to do some real (paid) work, the Tablescore is just an hobby, 
but I love it.
In the week-end I'll check the real lengths of (Autore , Lavoro, 
StrumentoGenerico, StrumentoSpecifico) and maybe remove some fields 
(Strumento*) from the ORDER BY.
Will let you know
and again THANKYOU.

[toc] | [prev] | [next] | [standalone]

#18422

FromJerry Stuckle <jstucklex@attglobal.net>
Date2020-11-20 11:05 -0500
Message-ID<rp8pg4$7f3$1@jstuckle.eternal-september.org>
In reply to#18420
On 11/20/2020 4:15 AM, J.O. Aho wrote:
> On 20/11/2020 08.36, cb wrote:
>> Jerry & J.O. thanks for your attention !
>>  >> How big is your database
>>     400.000 rows
>>  >> table definition:
>>   MYSQL
>>   "CREATE TABLE IF NOT EXISTS `Tablescore` (  \n"
>>                + "   `Progr` INT AUTO_INCREMENT ,  \n"
>>                + "   `Autore` varchar(512) NOT NULL,  \n"
>>                + "   `Lavoro` varchar(512) DEFAULT NULL,  \n"
>>                + "   `Link` varchar(512) DEFAULT NULL,  \n"
>>                + "   `NumFil` varchar(160) DEFAULT NULL,  \n"
>>                + "   `DateSubm` varchar(10) DEFAULT NULL,  \n"
>>                + "   `PlaceHolder` varchar(30) DEFAULT NULL,\n"
>>                + "   `StrumentoGenerico` varchar(130) DEFAULT NULL,\n"
>>                + "   `StrumentoSpecifico` varchar(130) DEFAULT NULL\n"
>>                + "   , PRIMARY KEY (Progr)) \n"
>>                + "ENGINE=InnoDB  DEFAULT CHARSET=latin1 
>> AUTO_INCREMENT=0 ";
> 
> I guess the multi key index is Autore , Lavoro, StrumentoGenerico, 
> StrumentoSpecifico and I think that in InnoDB you have a key limit of 
> 767 bytes, so as I can recall your key size would be
> 
> 512*2 + 512*2 + 130*1 + 130*1 = 2308
> 
> the two first one should automatically be made to double bytes as they 
> are larger than 255 chars. Lets see if Jerry agrees with that conclusion.
> 
> 
>> On another (italian) newsgroup I got the suggestion to use a temp table.
>> I'll try it during the week-end.
> 
> I doubt that would make much difference as you ain't joining things and 
> the temp table will be removed when the mysql session is terminated, so 
> on your second connection you can't use the data.
> 
> One alternative would be to do a select on the whole table, sort it in 
> php and store the result in a cache, how effective this is depends on 
> how often you write to the table as the cache needs to invalidated when 
> you do insert/update/delete.
> 
> Another alternative is to use a faster database server, like aws aurora, 
> but sure it comes with an extra cost.
> 


Maximum length for a single field is 767 bytes but for total keys it is 
3500 bytes (which MySQL limits to 3072 bytes).

But the columns themselves are not double byte.  When you go over 255 
bytes the length field at the beginning of the record expands from one 
to two bytes, but the rest of the column size is dependent only on the 
charset being used. Since this is latin1 the data would be single byte.
So the actual length of each field is 512 bytes for data + 2 bytes 
length field = 514 bytes.  Other fields would be 130 bytes for data + 1 
byte for length or 131 bytes.  So the total length would be 
514+514+131+131 or 1290 bytes.  An index on the four fields (Autore , 
Lavoro, StrumentoGenerico, StrumentoSpecifico) should help (I don't see 
any index listed).

The other consideration here is searching and sorting on character 
fields is much slower then integer fields.  If any of these fields 
contain the same information the database could be improved by 
normalizing it.

-- 
==================
Remove the "x" from my email address
Jerry Stuckle
jstucklex@attglobal.net
==================

[toc] | [prev] | [next] | [standalone]

#18423

FromArno Welzel <usenet@arnowelzel.de>
Date2020-11-20 17:42 +0100
Message-ID<i1q9vjFnnnhU1@mid.individual.net>
In reply to#18413
cb:

> 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.)

Without LIMIT pagination makes no sense. So yes - this is the correct
way to do it - set a limit and an offset for the records you want to
retrieve.

> 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.

Define "whole DB"? What *exactly* is your problem?

Doing subsequent queries for pagination is totally fine - that's the
reason why LIMIT and OFFSET exist. Is column1 indexed?


-- 
Arno Welzel
https://arnowelzel.de

[toc] | [prev] | [standalone]

Back to top | Article view | comp.lang.php

csiph-web