Newsgroup: it.comp.www.php
Titolo: Php/mysql paginare i risultati

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.

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" :-) :-(

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


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
 
 

Chiedo scusa a tutti x il disturbo, ma sembra che NON stia usando gli indici
Mi sa che devo definire una chiave multipla sui campi ORDER BY
Comunque il consiglio della TEMP table me lo segno e ...
GRAZIE ancora per l'attenzione !
:-(
P.S. usando i nomi (quasi) veri:
****
CREATE TABLE IF NOT EXISTS `Tablescore` (
                 `Progr` INT AUTO_INCREMENT ,
                 `Autore` varchar(512) NOT NULL,
                 `Lavoro` varchar(512) DEFAULT NULL,
                 `Link` varchar(512) DEFAULT NULL,
                 `NumFil` varchar(160) DEFAULT NULL,
                 `DateSubm` varchar(10) DEFAULT NULL,
                 `PlaceHolder` varchar(30) DEFAULT NULL,
                 `StrumentoGenerico` varchar(130) DEFAULT NULL,
                 `StrumentoSpecifico` varchar(130) DEFAULT NULL
                 , PRIMARY KEY (Progr))
              ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=0 ";
****
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    |    |
+--------------------+--------------+------+-----+---------+----------------+
****
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 <occaz..!| NULL<| NULL 
  | NULL | 381853 | Using filesort |
+------+-------------+------------+------+---------------+------+---------+------+--------+----------------+
 

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

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

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.