Query su singola tabella

[peppeocchi]

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

[table="width: 500, class: grid"]
[tr]
[td]id[/td]
[td]nome[/td]
[td]...[/td]
[td]valore[/td]
[td]data[/td]
[td]primary_id[/td]
[/tr]
[tr]
[td]58[/td]
[td]Marco[/td]
[td]...[/td]
[td]500[/td]
[td]20130505[/td]
[td]18[/td]
[/tr]
[tr]
[td]129[/td]
[td]Gianni[/td]
[td]...[/td]
[td]430[/td]
[td]20130505[/td]
[td]25[/td]
[/tr]
[tr]
[td]...[/td]
[td]...[/td]
[td]...[/td]
[td]...[/td]
[td]...[/td]
[td]...[/td]
[/tr]
[tr]
[td]58[/td]
[td]Marco[/td]
[td]...[/td]
[td]505[/td]
[td]20130506[/td]
[td]32[/td]
[/tr]
[tr]
[td]129[/td]
[td]Gianni[/td]
[td]...[/td]
[td]430[/td]
[td]20130506[/td]
[td]47[/td]
[/tr]
[tr]
[td]...[/td]
[td]...[/td]
[td]...[/td]
[td]...[/td]
[td]...[/td]
[td]...[/td]
[/tr]
[tr]
[td]58[/td]
[td]Marco[/td]
[td]...[/td]
[td]520[/td]
[td]20130507[/td]
[td]58[/td]
[/tr]
[tr]
[td]129[/td]
[td]Gianni[/td]
[td]...[/td]
[td]430[/td]
[td]20130507[/td]
[td]103[/td]
[/tr]
[tr]
[td]...[/td]
[td]...[/td]
[td]...[/td]
[td]...[/td]
[td]...[/td]
[td]...[/td]
[/tr]
[tr]
[td]58[/td]
[td]Marco[/td]
[td]...[/td]
[td]550[/td]
[td]20130508[/td]
[td]118[/td]
[/tr]
[tr]
[td]129[/td]
[td]Gianni[/td]
[td]...[/td]
[td]430[/td]
[td]20130508[/td]
[td]125[/td]
[/tr]
[tr]
[td]...[/td]
[td]...[/td]
[td]...[/td]
[td]...[/td]
[td]...[/td]
[td]...[/td]
[/tr]
[/table]

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]

Ciao, prova cosi

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]

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]

4 minuti sono tanti
ma per un giorno solo?
hai messo qualche indice sulla tabella?

 

[peppeocchi]

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]

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]

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

[table="width: 500, class: grid"]
[tr]
[td]id[/td]
[td]nome[/td]
[td]luogo[/td]
[td]variazione giorno 1[/td]
[td]variazione giorno 2[/td]
[td]variazione giorno 3[/td]
[td]variazione giorno 4[/td]
[/tr]
[tr]
[td]5[/td]
[td]Marco[/td]
[td]casa[/td]
[td]500[/td]
[td]500[/td]
[td]495[/td]
[td]492[/td]
[/tr]
[tr]
[td]10[/td]
[td]Marco[/td]
[td]lavoro[/td]
[td]300[/td]
[td]300[/td]
[td]300[/td]
[td]300[/td]
[/tr]
[/table]


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]

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]

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]

prova anche questa

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

 

[peppeocchi]

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

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.

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