Query su singola tabella

peppeocchi

Utente Attivo
20 Apr 2013
30
0
0
Salve,
ho una tabella con circa 70000 record. La tabella ha 14 campi, e i record sono divisi in 4 gruppi in base alle giornate. Ecco un esempio

idnome...valoredataprimary_id
58Marco...5002013050518
129Gianni...4302013050525
..................
58Marco...5052013050632
129Gianni...4302013050647
..................
58Marco...5202013050758
129Gianni...43020130507103
..................
58Marco...55020130508118
129Gianni...43020130508125
..................

Ho uno script php di ricerca che va a selezionare solo i record che non hanno avuto variazioni nel campo "valore", script che nel caso medio esegue circa 100 query.

Vorrei ottimizzare lo script per evitare di fare tutte queste query per ogni ricerca, e ho pensato di creare una tabella separata contenente tutti gli utenti che non hanno avuto variazioni nel campo "valore" nelle diverse date.

Qualche idea? Ho già provato con una join sulla stessa tabella....mai visto il risultato finale, dopo diversi minuti riavvio apache perchè mi si blocca tutto.
 

criric

Super Moderatore
Membro dello Staff
SUPER MOD
MOD
21 Ago 2010
5.607
54
48
TN
Ciao, prova cosi
Codice:
SELECT * FROM (
SELECT *,min(valore) as minimo,max(valore) as massimo 
FROM tabella
GROUP BY id
) as subtab
WHERE minimo = massimo && data = '2013-05-05'
 

peppeocchi

Utente Attivo
20 Apr 2013
30
0
0
Grazie mille, ho fatto qualche prova e sembrerebbe funzionare.
Unica pecca, il tempo di esecuzione, siamo sui 4 minuti abbondanti.
Continuo a fare prove per vedere se i risultati sono giusti, grazie.
 

criric

Super Moderatore
Membro dello Staff
SUPER MOD
MOD
21 Ago 2010
5.607
54
48
TN
4 minuti sono tanti
ma per un giorno solo?
hai messo qualche indice sulla tabella?
 

peppeocchi

Utente Attivo
20 Apr 2013
30
0
0
Si, 4 minuti sono tanti, ma ho fatto la prova inserendo la query così come l'hai postata, quando in realtà è molto più complessa e piena di filtri.
Modificando la tua query con i miei filtri, una ricerca media (in locale) mi impiega meno di 1 secondo, rispetto ai 3-4 secondi che impiegava prima.

Però adesso ho un'altra domanda, se volessi fare il confronto solo sui valori di determinate date, e non di tutte e quattro? E se volessi prendere anche i valori che avevano nelle altre date?
Mi spiego con un esempio.

giorno 1 -> valore 500
giorno 2 -> valore 500
giorno 3 -> valore 495
giorno 4 -> valore 492

se volessi prendere in considerazione solo giorno 1 e giorno 2? però mandare in output anche i valori che hanno in giorno 3 e giorno 4. Timestamp suppongo....Purtroppo nel campo data non ho timestamp, ma int tipo 20130505....dici di convertire tutto in timestamp per facilitare i calcoli?

In pratica, faccio il confronto solo per le prime due date e ho un output simile

Marco 500 500 (495) (492)


Ultima domanda.
Se l'utente ha più record inseriti per lo stesso giorno, e in output voglio mandare solo gli utenti che non hanno variazioni sul campo valore (quindi non solo i singoli record).
Esempio

id nome luogo ... valore data
5 Marco casa .... 500 20130505
6 Marco casa .... 200 20130505
5 Marco casa .... 505 20130506
6 Marco casa .... 200 20130506

In questo caso il record con id 5 non verrebbe mostrato, ma il record con id 6 verrebbe mostrato. Il mio obiettivo è escludere l'utente se anche uno solo dei suoi luoghi ha subito variazioni.

Grazie ancora, la tua prima risposta mi è già stata utilissima!
 

Longo8

Utente Attivo
28 Mar 2011
1.694
0
36
Ciao, cosa intendi con:
se volessi prendere in considerazione solo giorno 1 e giorno 2? però mandare in output anche i valori che hanno in giorno 3 e giorno 4.
Per mandarli in output non li puoi escludere quindi li devi comunque prendere in considerazione.
 

peppeocchi

Utente Attivo
20 Apr 2013
30
0
0
Come dicevo nel primo post, sto cercando di ottimizzare uno script che in media fa 100 query per ogni ricerca.
Spiego brevemente perchè tutte queste query.

La prima, filtra tutti i risultati prendendo solo i record che non hanno subito variazioni. In media questa query, con tutti i filtri, restituisce una tabella di 100 risultati.
Quando mando in output il risultato è una tabella di questo tipo

idnomeluogovariazione giorno 1variazione giorno 2variazione giorno 3variazione giorno 4
5Marcocasa500500495492
10Marcolavoro300300300300


Quindi per ogni id risultante dalla prima query, faccio una query per selezionare il campo valore dei giorni precedenti, e se gli id sono 100, ci sono altre 100 query da fare.
Il mio obiettivo sarebbe quello di ottenere questi valori con la prima query evitando di eseguire una ulteriore query per ogni record.

Per rispondere alla tua domanda, con la query (utilissima tra l'altro) che mi hai dato recupero solo i recordi di chi nei 4 giorni ha una variazione pari a 0, questa variazione può essere tra giorno 1 e giorno 4, tra giorno 3 e giorno 4 eccetera.
All'utente faccio decidere in quale range eseguire la ricerca, se completa nei 4 giorni, se invece solo negli ultimi 3 giorni, o solo negli ultimi 2 giorni.
Nell'esempio sopra sto simulando una variazione solo nei giorni 1 e 2, se invece l'utente decide di fare la ricerca in tutti e 4 i giorni, il primo record verrebbe escluso, e con la tua query il risultato sarebbe assicurato.
E' anche vero però che io dovrei mandare in output solo gli utenti che non hanno subito variazioni in nessuno dei luoghi, quindi la query corretta dovrebbe escludere l'utente Marco.

Comunque, il problema principale resta quello di evitare le 100 query a ricerca e recuperare i valori dei giorni precedenti con la prima query.

Scusa, il discorso è un po' incasinato, se vuoi ti passo il link del sito e vedi come dovrebbe essere l'output.

Grazie ancora
 

borgo italia

Super Moderatore
Membro dello Staff
SUPER MOD
MOD
4 Feb 2008
16.046
150
63
PR
www.borgo-italia.it
ciao
...ma int tipo 20130505....dici di convertire tutto in timestamp...
guarda che non devi convertire in timestamp è gia un numero intero che rappresenta una data espressa in timestamp.
quindi puoi confrontarla con una data che vuoi tu espressa es nel tipo gg.mm.YY e trasformata in timestamp usando mktime.
es se vuoi i record di un particolare giorno
$da=mktime ([ 0, 0 ,0, 4, 25, 2013);
$da=mktime ([ 23, 59 ,59, 4, 25, 2013);
e con BETWEN estrai i record del 25 aprile
 

peppeocchi

Utente Attivo
20 Apr 2013
30
0
0
In realtà mi sono confuso io, perchè la data la tengo solo per l'aggiornamento automatico (può capitare che eseguo l'aggiornamento più volte nello stesso giorno perchè alcuni file .sql che vado a prendere possono non essere pronti quando parte il primo cron, in modo da evitare di sovrascrivere tabelle già aggiornate).

Per navigare tra le date ho un campo id_update autoincrement.

Tengo nel database solo gli ultimi 4 giorni, la ricerca non la faccio per un giorno specifico, o meglio, la faccio per il giorno attuale più il/i giorno/i precedente/i, che gestisco tramite id_update.

Grazie, interessante funzione, non mi era mai capitato di usarla, peccato che non posso usarla in questo caso perchè può capitare che i file .sql un giorno non siano disponibili, e quindi la ricerca non andrebbe a buon fine
 

criric

Super Moderatore
Membro dello Staff
SUPER MOD
MOD
21 Ago 2010
5.607
54
48
TN
prova anche questa
Codice:
SELECT G.id,G.nome,G1.valore as giorno1,
G2.valore as giorno2,G3.valore as giorno3,G4.valore as giorno4
FROM tabella G
JOIN tabella G1 ON G1.id = G.id && G1.data = '2013-05-05'
JOIN tabella G2 ON G2.id = G.id && G2.data = '2013-05-06'
JOIN tabella G3 ON G3.id = G.id && G3.data = '2013-05-07'
JOIN tabella G4 ON G4.id = G.id && G4.data = '2013-05-08'
WHERE G.data BETWEEN '2013-05-05' AND '2013-05-08'
GROUP BY G.id
ho usato una data nel formato DATE di mysql ma puoi benissimo adattarla alla tua data
cmq se usi un campo intero ti conviene inserire il timestamp ricavato con php come suggerito da Borgo incece che (20130505) non riscirai mai a fare dei calcoli corretti su una data memorizzata in questo modo
altrimenti ti conviene usare il TIMESTAMP di mysql o DATE come ho usato io nell'esempio sopra ('2013-05-08')
ps
cmq secondo me è meglio fare 100 query da un secondo che una da 100 secondi
 
Ultima modifica:

peppeocchi

Utente Attivo
20 Apr 2013
30
0
0
Come dicevo prima la data è usata solo per evitare duplicati, per navigare tra i giorni ho un campo "id_update", anche perchè come dicevo può capitare che ci sia un giorno buco senza aggiornamenti.

Ho provato la query adattata con id_update anzichè la data, ma il risultato è 0 righe.

Come avevo provato anche in passato, per recuperare il "valore" dei giorni precedenti devo fare una join, troppo costosa (4 join su una tabella di 70000 record).

Ho provato anche a spezzettare la tabella in base a id_update

Codice:
select * from ( select * from tabella where id_update = 160 ) as t1, (....) as t2 eccetera

ma anche in questo caso l'operazione è troppo costosa, quindi come consigli anche tu, resto con le 100 query.

Visto che non c'è modo di ridurre il numero di query, spero ci sia il modo di alleggerire la query principale.

Codice:
SELECT * 
FROM (
	SELECT * , ( ROUND( ( SQRT( POW( x - ( $x_value ) , 2 ) + POW( y - ( $y_value ) , 2 ) ) ) , 2 ) ) AS distance
	FROM $server
	GROUP BY id
) AS subNear
WHERE idUser IN (
	SELECT idUser
	FROM (
		SELECT idUser, MIN( totale ) AS minimo, MAX( totale ) AS massimo
		FROM (
			SELECT * , SUM( pop ) AS totale
			FROM $server
			GROUP BY idUser, id_update
		) AS subUser
		GROUP BY idUser
	) AS subFilter
	WHERE ( massimo - minimo ) < $max_variation
)
AND distance < $max_distance
ORDER BY distance

Con questa query prendo esattamente i record che mi servono (escludendo quindi anche la variazione per ogni utente anche se ha più luoghi e alcuni di questi variano)
In locale ci mette 9,5s che è troppo, ovviamente ho fatto la prova con il caso peggiore.

Qualche idea su come alleggerirla? Come avrete capito non sono certo un asso in SQL...

Grazie
 
Discussioni simili
Autore Titolo Forum Risposte Data
max75 query su due tabelle e dati doppioni MS Access 2
E Aiuto per query PHP 8
E Query differenza tra tabelle MySQL 5
E Query Alias PHP 4
T Query con date MS Access 1
K Inserimento query Errore 1366 PHP 4
L Query e selezione DB con $_POST PHP 2
M query update e delete PHP 0
A media degli elementi estratti da una query MySQL 0
S Passare query o utm alla pagina successiva Domini 1
O problema con query PHP 4
M Query PHP 8
R query DELETE non cancella i record PHP 1
G Query con i giorni della settimana MySQL 10
E Query annidate o cicli MySQL 1
C bottone query MySQL 0
T Query su due tabelle con totali Database 4
G AVVISO QUERY IN ESECUZIONE PHP 1
M Riportare in tabella HTML dati estratti con query SQL MySQL 0
S Selezionare ultimo record di una sotto query MySQL 27
G Menù a tendina di ricerca con query PHP 1
V Query per una somma PHP 2
F stampare a video i risultati della query PHP 1
L problema con query select PHP 2
D Query sum Database 1
M Visualizzazione a video risultati query php PHP 0
A form PHP prenotazione tramite query PHP 2
R Problema query con ricerca id numerico PHP 2
G Casella combinata in una query MS Access 4
R Aiuto con le query MS Access 2
Y query di DELETE PHP 4
V Query Oracle Oracle 0
L query somma PHP 8
W Query SQL Classic ASP 2
M Problemi con query a più tabelle PHP 3
Alex_70 Conteggio nella query PHP 0
elpirata Query per leggere dati da una tabella mysql e mostrarli a video in base a parametri passati tramite GET PHP 5
A nome file che contiene punto passato come parametro a query PHP 31
F query e form con select multipla PHP 17
M Problema su query JOIN in tre tabelle PHP 0
M Ordinare una query join PHP 2
Alex_70 Query ORDER BY PHP 18
G Problema ------- con Query PHP 1
G Problema con Query PHP 1
Alex_70 foreach explode query PHP 3
O query con date in formato stringa PHP 3
Tommy03 Unione query di 4 tabelle MySQL 1
I Fatal error: Query Failed! SQL: SELECT * INTO OUTFILE PHP 1
Alex_70 Query group actor e film PHP 26
TpD Incolonnare risultati query (mysql_result deprecato...) PHP 3

Discussioni simili