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

Groups > comp.lang.php > #18420

Re: Php/Mysql paginate results without resubmitting query

Show all headers | View raw

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

Back to comp.lang.php | Previous | Next — Previous in thread | Next in thread | Find similar | Unroll thread

Thread

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

csiph-web