query lenta a ragione

[bastian70]

sono un principiante che fino a 15 gg fa usava access dunque scusate le inesattezze

ho due tabelle 25.000 e 20.000.000 di record
una query INNER JOIN mi ricava varie count sum max avg ma è lentissima ed eccede il tempo massimo di php
Ho appena letto la pillola per ottimizzare MYSQL e metterò in pratica ma avrei deciso di salvare la query in una tabella da aggiornare ogni giorno - Che ne pensate ?
come posso fare? perchè appena aggiungo --- INTO nuovatab --- phpMyAdmin mi da errore
Grazie a tutti

[marketto]

Che tipo di query solo? Le tabelle hanno degli indici?

[bastian70]

speravo in una risposta grazie
tabella1 ha una chiave primaria user altri campi prov e id
tabella2 nessun indice
la query è:
SELECT tabella1.user, tabella1.prov, Year(tabella2.op), Count(tabella2.ID), Count(tabella2.importo), Sum(tabella2.importo), Min(tabella2.importo), Max(tabella2.importo), Avg(tabella2.importo) FROM tabella1 INNER JOIN tabella2 ON tabella1.ID = tabella2.ID GROUP BY tabella2 .ID, Year(datav);
in tabella1 ID è unico e non nullo
in tabella2 ID è duplicato e non nullo
mi serve lavorare spesso su questa tabella da php ordinarla in un modo e poi in un altro ecc.
Considerato che i dati del db cambiano lentamente come faccio a generare la tabella e ad aggiornala automaticamente ogni giorno?

[webus]

per una risposta più completa servirebbero altre informazioni, tipo se hai accesso al server, quale versione etc. ma in mancanza...

Ad ogni modo, tra le varie cose che potresti fare, la tabella la puoi creare così:

CREATE TABLE t_report
SELECT 
    tabella1.user, 
    tabella1.prov, 
    Year(tabella2.op), 
    Count(tabella2.ID), 
    Count(tabella2.importo), 
    Sum(tabella2.importo), 
    Min(tabella2.importo), 
    Max(tabella2.importo), 
    Avg(tabella2.importo) 
FROM tabella1 
    INNER JOIN tabella2 ON tabella1.ID = tabella2.ID 
GROUP BY tabella2.ID, Year(datav); 

Imposta la colonna user come PRIMARY.

E questa è la query per gli aggiornamenti:

REPLACE INTO t_report
SELECT 
    tabella1.user, 
    tabella1.prov, 
    Year(tabella2.op), 
    Count(tabella2.ID), 
    Count(tabella2.importo), 
    Sum(tabella2.importo), 
    Min(tabella2.importo), 
    Max(tabella2.importo), 
    Avg(tabella2.importo) 
FROM tabella1 
    INNER JOIN tabella2 ON tabella1.ID = tabella2.ID 
GROUP BY tabella2.ID, Year(datav); 

[skidx]

se hai almeno il MySQL 5, secondo me ti conviene crearti una tabella "parallela" a tabella 2 contenente solo i dati aggregati (min(), max(), avg() etc. etc.) e la chiave primaria, e poi aggiungere un trigger a tabella2 che aggiorni di volta in volta solo i record interessati da modifiche (o se non ti garba il trigger o hai MySQL 4 puoi fare altrettanto tramite PHP), altrimenti ogni giorno ti ritrovi a dover eseguire una query mastodontica.

[webus]

Originariamente inviato da skidx
ogni giorno ti ritrovi a dover eseguire una query mastodontica.

vabbé, dopotutto si tratta solo di processare 500.000.000.000 (25.000 * 20.000.000) di righe

Il calcolo è ipotizzato assumendo che datav sia nella tabella1, altrimenti potrebbero essere molte meno.

[skidx]

Originariamente inviato da webus
vabbé, dopotutto si tratta solo di processare 500.000.000.000 (25.000 * 20.000.000) di righe

Infatti, e quindi il timeout del PHP è il problema minore che può capitargli

bastian70, non so a cosa ti serva nello specifico questa funzionalità, se quei dati ti servono per fare analisi statistiche, cioè quella che in maniera trendy viene definita "business intelligence" per sistemi decisionali, in genere quei sistemi usano database totalmente separati da quelli "online", strutturati in maniera diversa (perché ci devi fare cose diverse) e che vengono aggiornati in maniera asincrona con dati aggregati.

Esempio: un grande e-commerce. Rallentare tutte le operazioni online degli utenti per produrre questi dati aggregati che servono ai gestori per valutare le strategie di vendita può essere sconveniente, meglio avere un database separato da aggiornare periodicamente strutturato in maniera utile per le analisi aggregate.

[bastian70]

datav sta in tabella2
Attualmente la query richiede circa 100sec per essere eseguita e mi serve per effettuare analisi e scelte statistiche.

L'onere di aggiornare i dati delle due tabelle è attualmente affidato ad un prog java che gira in modo continuo, considerato che la notte il carico non è elevato avevo pensato di aggiungere un controllo sulla data di ultimo aggionamento della tabella "aggregata" quando rileva una data inferiore a quella corrente esegue la query scritta da webus.
Php al momento esegue solo select dal database.

Tenete presente che non sono un prog e sopratutto sono un principiante perchè fino a 12.000.000 di record ho gestito tutto da access e solo ora per necessità scopro un mondo meraviglioso qundi grazie il vostro prezioso aiuto.

[daniele_dll]

ciao,

senti quali campi usi, sia della prima tabella sia della seconda, usi nel where e nella inner join? Potresti postare la struttura delle tabelle (se puoi )?

Considera che una grossissima mazzata è data dalla mancanza di indici e mysql è quindi lento da far paura a fare la join

Alternativamente perché non fai una view?

http://dev.mysql.com/doc/refman/5.0/en/create-view.html

Puoi usarle però solo se hai mysql 5, in questo modo ogni volta che fai un'operazione sulle tabelle mysql ti aggiorna la view. E' ovviamente importantissimo che le tabelle siano strutturate correttamente, ergo devono starci gli indici assolutamente

Se poi i tempi non si riducono consistentemente anche con gli indici allora ti consiglio anch'io di passare ad una seconda tabella

[webus]

daniele, non credo che gli cambi molto usare una view, e probabilmente nemmeno creando indici aggiuntivi.

Dal momento che datav è nella tabella2, l'unico indice utilizabile dalla query sopra resta quello di user in tabella1. In tabella2 invece nessun indice può essere usato dal momento che vendono impiegate delle function che ne inibiscono l'uso.

Forse otterrebbe qualche incremento di prestazioni assegnando una cache specifica a tabella1 e tabella2, ma servirebbe sapere se possiede privilegi GRANT o meno.

Un'altra possibilità è il ricorso a una summary table dove tenere le singole righe, senza dati aggregati, che otterrebbe dalla query, ma siamo sempre solo nel cxampo delle ipotesi.

Ad ogbni modo, 100 sec. per processare diversi miliardi di righe (nella migliore delle ipotesi) non mi pare malaccio.