Php/mysql paginare i risultati

cb

unread,

Nov 19, 2020, 11:53:21 AM11/19/20

to

Chiedo il vostro aiuto
Ho una select che mi ritorna una grossa mole di dati
SELECT column1,column2 FROM table ORDER BY column1 LIMIT $count OFFSET
$offset;
Senza il LIMIT viene cancellata per time limit exceeded e piacevolezze
del genere
L'utente poi puo' cambiare il LIMIT ed il COUNT.
Ma sono costretto a ri-sottometere la query che a causa dell' ORDER BY
mi scorre tutto il DB.
(Si' column1 e column2 sono indicizzate)
C'e' un modo per salvare i risultati della query (senza LIMIT/OFFSET) da
qualche parte e poi "navigare" in questo salvataggio senza tampinare
piu' il DB ?
I risultati delle query li estraggo in questo modo
$rc = mysqli_query($db, $qlib);
while ($row = mysqli_fetch_array($rc)) { array_push($righe, $row); }
Ma se $qlib non ha il LIMIT mi viene piallata.
GRAZIE per la pazienza.

Leonardo Serni

unread,

Nov 19, 2020, 6:39:27 PM11/19/20

to

On Thu, 19 Nov 2020 17:53:20 +0100, cb <c...@ppt.io.it> wrote:

>Ho una select che mi ritorna una grossa mole di dati
>SELECT column1,column2 FROM table ORDER BY column1 LIMIT $count OFFSET
>$offset;
>Senza il LIMIT viene cancellata per time limit exceeded e piacevolezze
>del genere
>L'utente poi puo' cambiare il LIMIT ed il COUNT.
>Ma sono costretto a ri-sottometere la query che a causa dell' ORDER BY
>mi scorre tutto il DB.

La cosa si può fare, ma non in modo pulito.

Il fatto è che il resultset ottenuto dalla tua query va perduto non appena la
pagina ha terminato di caricarsi; quindi, appena vedi le prime 50 righe, ecco
che le righe da 51 a 200000 non esistono più.

C'è un sistema molto brutto che consente di fare funzionare la cosa usando un
processo worker, AJAX e un CURSOR lato MySQL. Però è complicato, difficile da
gestire, e i cursori di MySQL sono asensitivi e monotoni: questa seconda cosa
significa che se vai da pagina 1 a pagina 2 tutto bene - ma indietro non puoi
tornare.

Quindi l'alternativa dolorosa è stivare i dati in un'area temporanea, per poi
accedere a quella.

I sistemi sono due. Fanno schifo entrambi.

Col primo sistema crei una tabella di appoggio con la tua query... ma siccome
ti servirà di paginarla, ti serve un numero di riga perché, altrimenti, torni
a dover usare OFFSET e LIMIT e fai la fine di Raniere.

Quindi la tua

SELECT col1, col2

deve diventare ben tre query:

SET @recno = 0;
CREATE TABLE temp_018374 AS
SELECT col1, col2, @recno:=@recno+1 AS recno FROM (segue il resto
della query con ORDER ma senza LIMIT);
ALTER TABLE temp_018374 ADD recno PRIMARY KEY;

La tua paginazione poi si traduce in

SELECT * FROM temp_018374
WHERE recno BETWEEN {$pagestart} AND {$pageend}
ORDER BY recno;

e poi devi trovare il modo di fare un DROP TABLE temp_018374 a fine cose. Ti
può servire il comando "SHOW TABLES LIKE 'temp_%';".

Sono possibili variazioni e ottimizzazioni che però dipendono dalla query da
paginare (hint: puoi mettere in temp* le primary key che ti servono, fare la
paginazione da temp* e poi giocare di SELECT...WHERE primarykey IN (...) per
arricchire la pagina di righe. E' un casino fare 50 query? Sì. Ma sono tutte
query su primary key, ci sta che tu ci riesca agile.

Il secondo sistema è peggio ancora e prevede di prelevare tutte le righe, il
che richiede un processo secondario con set_time_limit(0) forkato, farci già
tutto il necessario per formattare i dati, e archiviare il risultato o in un
terrificante archivio di pagine (quindi, /tmp/paging/query1235/1/120.json ti
contiene la pagina 120, per dire) o in un terrificante database SQLite. Pure
lì ti tocca poi pulire.

Se no, sapendo esattamente com'è fatta la tabella e quante righe ha, e quali
query ci fai girare sopra (c'è una WHERE? Fatta come?), si può vedere se c'è
modo di fare qualcosa di meglio.

Oppure, in circostanze sufficientemente sfigate questo ti aiuterebbe assa':

OPTIMIZE TABLE `table`;

o all'estremo:

CREATE TABLE ciccio LIKE `table`;
INSERT INTO ciccio SELECT * FROM `table` ORDER BY column1;
ALTER TABLE `table` RENAME TO `table_old`;
ALTER TABLE `ciccio` RENAME to `table`;
-- see what I did there?

O magari è possibile sfruttare il partitioning, che ne so io?

Leonardo
--

"You all presumably know why" :-) :-(

cb

unread,

Nov 20, 2020, 2:09:46 AM11/20/20

to

MOLTO chiaro ed esauriente.
ti ringrazio per l'attenzione ed il tempo dedicati !
:-)

On 11/20/20 12:39 AM, Leonardo Serni wrote:
> La cosa si può fare, ma non in modo pulito.

... ecc.

Roberto Tagliaferri

unread,

Nov 20, 2020, 2:51:35 AM11/20/20

to

Quanti record?
Io per un problema diverso (ovvero primo blocco di annunci "premium"+blocco
annunci standard in ordine casuale) mi sono memorizzato nella tabellina che
uso per tenere traccia degli utenti la query get di ricerca e gli id
risultanti (in json).
Se cambia la ricerca rifaccio la query, altrimenti prendo direttamente gli
id

--
Roberto Tagliaferri-Linux user #30785 <-> r.tagliaferri@(forse)tosnet.it
www.robyt.eu

cb

unread,

Nov 20, 2020, 3:08:23 AM11/20/20

to

Leonardo Serni

unread,

Nov 20, 2020, 3:22:40 PM11/20/20

to

On Fri, 20 Nov 2020 09:08:21 +0100, cb <c...@ppt.io.it> wrote:

> `DateSubm` varchar(10) DEFAULT NULL,

Perché qui sopra non metti un DATETIME? O anche solo un DATE?

> `StrumentoGenerico` varchar(130) DEFAULT NULL,
> `StrumentoSpecifico` varchar(130) DEFAULT NULL

Veramente in questi campi ci può andare la qualunque? Non è che magari hai un
elenco di 20-30-500 strumenti generici e specifici?

Perché, se sì, un bel paio di tabelle collegate ci starebbero di nìdio. Anche
con una bella foreign key che non guasta mai.

(E per i sudici, una chiave intera da 1000 a 500000 messa nello stesso ordine
lessicografico del valore che mappa ]:-), così uno può fare un ORDER BY senza
bisogno di JOIN).

Sul perché non usi l'indice (a parte il fatto di non averlo, che è sempre una
ottima ragione): se fai una SELECT di ogni cosa, e tiri dentro circa tutte le
colonne, MySQL ovviamente non usa l'indice, perché cosa gli cambierebbe? E se
creassi un indice su quei VARCHAR così grassi, le prestazioni potrebbero pure
essere peggiori che un full table scan.

cb

unread,

Nov 21, 2020, 12:06:24 PM11/21/20

to

Vi ringrazio molto per l'aiuto fornitomi.
Purtroppo non avevo creato una chiave sui campi dell' ORDER BY
ero sicuro di averlo fatto, ma non era cosi'
Creato l'indice tutto funzia alla grande.
Chiedo davvero scusa x il disturbo

bramante

unread,

Nov 23, 2020, 11:35:51 AM11/23/20

to

Il 19/11/20 17:53, cb ha scritto:

ciao

se i dati sono finiti, anche se migliaia di record ma un resulset finito
(un elenco generico e non un newsfeed alla facebook per intenderci)

caricherei tutto senza limit e offset e li memorizzerei in formato json
come output.

e per la parte view li gestirei tramite javascript, andando a prendere
un tot alla volta, magari come gestione di pagine in una griglia
(tabellare) o come fondo pagina quando ad esempio scrolli fino a
caricare altri dati.

in questo modo la UI è molto più reattiva nel caricare i dati invece di
aspettare la chiamata lato server.

questo se parliamo di qualche migliaio di righe, se parliamo di centinai
di migliaia o milioni, allora gestirei man mano che l'utente li richiede
andando ad ottimizzare la ricerca lato mysql.

ma sempre con la tecnica descritta sopra.

un esempio
ho una possibile ricerca da milioni di occorrenze, ne estraggo 1000 alla
volta e ne faccio vedere 100 per volta tramite javascript.

una volta che raggiungo le 900 righe a video (la view) allora chiamo la
query lato server (una semplice chiamata Ajax) e accodo altre 1000 e
così via.

lato UI l'utente non attende che vengano caricate, per lui sono sempre
immediatamente disponibili.