Query annidate o Join per estrarre dato

[deleted_29]

il grafico fatto alla buona è
(blu query IN, rosso query JOIN)

http://www.iouppo.com/life/1101/f21b...746c32b609.jpg

Dove si vede bene quale sia l'andamento in funzione del numero di righe (nell'esempio fino a 100.000, devo andare a cena)

Ma qui c'è l'elemento per me interessante.

Fermo che si tratta di un test bovino, ossia fatto in fretta mentre mi occupavo di altro, come accennato fino a (nel mio caso) 17.000 righe circa... la query IN è più veloce della JOIN (!)

(blu query IN, rosso query JOIN)

http://www.iouppo.com/life/1101/6485...5dc340d298.jpg
A priori, l'avreste detto?

Non è immediato stabilirlo, ma, come già detto... l'uomo saggio... misura, e poi decide

PS ovviamente niente cache

PS2 altrettanto ovviamente manca la doppia-query INnata... ma si è fatto tardi!

[deleted_29]

Originariamente inviato da Samleo
In effetti hai ragione, magari sbagliavo io nel riportare i campi.

In ogni caso, essendo che questo è solo un pezzo di una query più grossa, mi viene meglio ad integrare quella con la IN.

In ogni caso grazie a tutti!

Ti suggerisco di verificare la query del tuo amico, perchè a me risulta dare risultati diversi, e non ho tempo di guardare come "funziona".

ti segnalo, così per avere un'idea, che con 30.000 righe è circa 700 volte PIU' lenta del join, e circa 300 più lenta della mia.
Parliamo di decine di secondi contro decine di millisecondi.
Con righe molto più numerose... non ho tempo di fare le prove ma la situazione peggiora enormemente

[nicola75ss]

Non ho capito qual'è la query che indichi come "in veloce"

[deleted_29]

Originariamente inviato da nicola75ss
Non ho capito qual'è la query che indichi come "in veloce"

la mia, ovviamente

Questa

SELECT *
FROM   ps_order_history p1
WHERE  (date_add=(SELECT MAX(p2.date_add)
              FROM ps_order_history p2
              WHERE p1.id_order=p2.id_order))
and (id_order_state=6)

che è, per quanto ne so, il modo vagamente "meno pessimo" ("meno pessimo" non si dice, ma spero si capisca) per scrivere una query annidata mysql per la domanda del thread.
---
Poi, a dir la verità, c'è sempre la questione potenzialmente interessante della doppia-query-con-taglio-nanto ( ) ossia
eseguire la query interna, fare il GROUP_CONCAT delle chiavi (ritornate ad una applicazione) e poi fare la query esterna mettendo come IN () la serie delle chiavi.
Dalla versione 4.02 (non mi ricordo più bene) non ci sono problemi con IN lunghi, e tra l'altro vengono ordinati (costo log n).

Ma, in questo caso, si avrebbero due query e non so quanto potrebbe essere interessante

-------
EDIT: mi rendo conto che sto dando per scontato di chiamare "IN veloce" la query perchè ha una DEPENDENT SUBQUERY (il che, per me, la classifica nella stessa tipologia) "under the hood"

Ovviamente la IN "vera" (ossia select... where ... IN (select blablabla......))" manco la considero, per i motivi già spiegati.
Lo davo per scontato ma forse è meglio chiarirlo

----
EDIT2: giusto per curiosità confermi che la "mia" query (per qualche migliaio di righe) è più veloce della "tua" ? (i parametri che entrano in gioco sono molto numerosi, e vorrei evitare di dire sciocchezze giganti, visto che ho poco tempo per far prove e soprattutto pensare

[deleted_29]

sooobbhhh... son costretto a sorbirmi... AMICI... ( )

[nicola75ss]

Originariamente inviato da franzauker

EDIT: mi rendo conto che sto dando per scontato di chiamare "IN veloce" la query perchè ha una DEPENDENT SUBQUERY (il che, per me, la classifica nella stessa tipologia) "under the hood"

Ah ecco. Per quello non riuscivo a capire a quale query ti stessi riferendo.

Originariamente inviato da franzauker
EDIT2: giusto per curiosità confermi che la "mia" query (per qualche migliaio di righe) è più veloce della "tua" ? (i parametri che entrano in gioco sono molto numerosi, e vorrei evitare di dire sciocchezze giganti, visto che ho poco tempo per far prove e soprattutto pensare

Hai indicizzato dei campi per la soluzione che hai proposto?

[deleted_29]

Originariamente inviato da nicola75ss

Hai indicizzato dei campi per la soluzione che hai proposto?

non ho fatto nulla più di usare il db di prova che hai messo.
copia-incolla, control-invio e basta

questa è la situazione corrente

CREATE TABLE `ps_order_history` (
`id_order_history` int(10) unsigned NOT NULL AUTO_INCREMENT,
`id_employee` int(10) unsigned NOT NULL,
`id_order` int(10) unsigned NOT NULL,
`id_order_state` int(10) unsigned NOT NULL,
`date_add` datetime NOT NULL,
PRIMARY KEY (`id_order_history`),
KEY `indice` (`id_order`,`date_add`)
) ENGINE=MyISAM AUTO_INCREMENT=2550501 DEFAULT CHARSET=utf8'

---
Al crescere delle righe, ovviamente, la differenza diventa incomparabile.
Con 1.000.000 di righe si vede come la crescita "quasi" lineare della join "prenda il volo"

select SQL_NO_CACHE t1.* from ps_order_history as t1
inner join (
select id_order_history
from ps_order_history
order by `date_add` desc) as t2
on t1.id_order_history = t2.id_order_history
group by t1.id_order
having id_order_state = 6

Z:\data\pluto>ptime mysql -uroot -p pluto <f:\join.sql

ptime 1.0 for Win32, Freeware - http://www.pc-tools.net/
Copyright(C) 2002, Jem Berkes <jberkes@pc-tools.net>

===  mysql -uroot -p1 pluto  ===
id_order_history        id_employee     id_order        id_order_state  date_add
2985956 6       5       6       2011-12-31 21:34:00
2936259 4       10      6       2011-12-31 23:56:26
2597744 9       39      6       2011-12-31 23:25:55
3333550 9       42      6       2011-12-31 23:35:40
2710505 10      44      6       2011-12-31 23:45:19
3164700 6       45      6       2011-12-31 23:02:49
3149934 7       50      6       2011-12-31 22:17:55
3364834 9       82      6       2011-12-31 22:28:30

Execution time: 5.760 s

rispetto alla subquery dipendente
(cosa interessante, almeno dà gli stessi risultati)

SELECT SQL_NO_CACHE *
FROM   ps_order_history p1
WHERE  (date_add=(SELECT MAX(p2.date_add)
              FROM ps_order_history p2
              WHERE p1.id_order=p2.id_order))
and (id_order_state=6)

Z:\data\pluto>ptime mysql -uroot -p pluto <f:\inveloce.sql

ptime 1.0 for Win32, Freeware - http://www.pc-tools.net/
Copyright(C) 2002, Jem Berkes <jberkes@pc-tools.net>

===  mysql -uroot -p1 pluto  ===
id_order_history        id_employee     id_order        id_order_state  date_add
2597744 9       39      6       2011-12-31 23:25:55
2710505 10      44      6       2011-12-31 23:45:19
2936259 4       10      6       2011-12-31 23:56:26
2985956 6       5       6       2011-12-31 21:34:00
3149934 7       50      6       2011-12-31 22:17:55
3164700 6       45      6       2011-12-31 23:02:49
3333550 9       42      6       2011-12-31 23:35:40
3364834 9       82      6       2011-12-31 22:28:30

Execution time: 193.779 s

che mi dici? se sei davvero curioso di fare qualche raffronto, magari ti posto il dump del mio db di prova

[nicola75ss]

Ah ok.
La tua query con soli 100 mila record impiega una media di circa 45 secondi.
L'ultima che ho postato, testata su un campione di 10 milioni di record, non va mai oltre i 15 millesimi di secondi. Quindi consiglierei anche a Samleo di utilizzare quella.

[deleted_29]

Originariamente inviato da nicola75ss
Ah ok.
La tua query con soli 100 mila record impiega una media di circa 45 secondi.
L'ultima che ho postato, testata su un campione di 10 milioni di record, non va mai oltre i 15 millesimi di secondi. Quindi consiglierei anche a Samleo di utilizzare quella.

qualcosa non mi torna, mi sembrava di aver usato la medesima tua query (solo con NO_CACHE) e mi impiega secondi!

select SQL_NO_CACHE t1.* from ps_order_history as t1
inner join (
select id_order_history
from ps_order_history
order by `date_add` desc) as t2
on t1.id_order_history = t2.id_order_history
group by t1.id_order
having id_order_state = 6

non è questa la tua?

A me su questo db
www.franzauker.com/pluto.7z
di 500.000 righe impiega 2.5 secondi circa.
Confermi?

[nicola75ss]

Originariamente inviato da franzauker
qualcosa non mi torna, mi sembrava di aver usato la medesima tua query (solo con NO_CACHE) e mi impiega secondi!

select SQL_NO_CACHE t1.* from ps_order_history as t1
inner join (
select id_order_history
from ps_order_history
order by `date_add` desc) as t2
on t1.id_order_history = t2.id_order_history
group by t1.id_order
having id_order_state = 6

non è questa la tua?

A me su questo db
www.franzauker.com/pluto.7z
di 500.000 righe impiega 2.5 secondi circa.
Confermi?

Inizialmente avevo suggerito quella query però quella a cui mi riferisco è l'ultima che avevo postato insieme alla stored procedure.

select t1.* from ps_order_history as t1
inner join (
select id_order,max(`date_add`) as `date_add`
from ps_order_history
group by id_order) as t2
on t1.id_order = t2.id_order and t1.`date_add` = t2.`date_add`

Questa è velocissima. Adesso sto uscendo.
Con calma poi mi leggo tutto ciò che hai scritto.