[SQL-MYSQL] Query annidata lentissima!Motivi?

[sonic85]

Ciao a tutti,
ho un quesito da porvi... Sto costruendo un db con interfaccia web con php e Mysql, in cui sono presenti delle pubblicazioni su riviste e gli autori di esse, divisi in ricercatori e autori esterni.

Le tabelle sn così formate: Pubblicazioni - AutoriPubblicazioni - Ricercatori.

Ed ho scritto questa query per trovare i ricercatori che sono in ultima posizione tra gli autori.

SELECT count(*) as 'ultimo' 
FROM (autoripubblicazioni inner join ricercatori on autoripubblicazioni.idricercatorefk=ricercatori.idricercatore) inner join pubblicazioni on autoripubblicazioni.idpubblicazionefk=pubblicazioni.idpubblicazione 
WHERE idricercatorefk IS NOT NULL AND 
ricercatori.idUOFK=16 AND anno=2007 AND 
(appendice='N') AND (idpubblicazionefk,posizione) 
IN 
(SELECT idpubblicazionefk, MAX(posizione) as 'test' 
FROM autoripubblicazioni 
GROUP BY idpubblicazionefk 
HAVING test>2 )

Ma è lentissima questa query! ci mette oltre 16 secondi per eseguirla!!!

e la devo lanciare tantissime volte per tutti autori che sono circa 200!!!

Come mai? cosa sbaglio? Consigli? Suggerimenti?


PS. Utilizzo MYSQL 5.0 con InnoDB

[gugoXX]

Fanne una sola che ti restuisca tutto insieme.
Scommetto che magari 16 secondi no, ma 30 per tutti insieme puo' anche farcela.

[cdimauro]

Credo che il problema stia nella subquery usando la IN.

L'ottimizzatore (per modo di dire) di query di MySQL fa letteralmente cacare e capita spesso che esegua una full table scan PER OGNI RECORD della prima tabella / join.

In ufficio ce ne accorgiamo quando vediamo che un processo appeso da più di un'ora (abbiamo tabelle LEGGERMENTE grandi ) succhia CPU come un dannato e rallenta, o addirittura blocca, le altre query, per cui dobbiamo ammazzare la query incriminata.

Nello specifico il mio consiglio è di far diventare la subquery una derived-table, e quindi effettuarci una JOIN coi campi interessati. Funzionalmente è la stessa cosa, ma in termini prestazionali siamo un po' di parsec avanti.

[taurus]

..e per piacere, togli tutte quelle parentesi, non si possono guardare e formatta meglio la join...

[gugoXX]

SELECT ricercatori.idUOFK, anno, count(*) as 'ultimo' 
FROM (autoripubblicazioni inner join ricercatori on autoripubblicazioni.idricercatorefk=ricercatori.idricercatore) inner join pubblicazioni on autoripubblicazioni.idpubblicazionefk=pubblicazioni.idpubblicazione 
WHERE idricercatorefk IS NOT NULL AND 
appendice='N' AND (idpubblicazionefk,posizione) 
IN 
(SELECT idpubblicazionefk, MAX(posizione) as 'test' 
FROM autoripubblicazioni 
GROUP BY idpubblicazionefk 
HAVING test>2 )
GROUP BY ricercatori.idUOFK, anno

[sonic85]

Originariamente inviato da taurus

..e per piacere, togli tutte quelle parentesi, non si possono guardare e formatta meglio la join...

scusa, ma che c'è che nn va??

[pabloski]

Originariamente inviato da cdimauro

L'ottimizzatore (per modo di dire) di query di MySQL fa letteralmente cacare e capita spesso che esegua una full table scan PER OGNI RECORD della prima tabella / join.

eh mysql!!!! farò una statua ad Ellison se riuscirà a farlo sparire

postgresql for ever

[sonic85]

Ho fatto un pò di prove e ho visto che modificandola nel seguente modo è MOLTO + veloce!
Al massimo 1.2818 secondi... rispetto ai agli oltri 16 sec!
Penso proprio che il problema sia nella Inner join con 3 tabelle... sbaglio?


Visualizzazione record 0 - 0 (1 Totali, La query ha impiegato 1.2818 sec)

SELECT COUNT( idpubblicazionefk ) AS  'ultimo'
FROM autoripubblicazioni
INNER JOIN ricercatori ON autoripubblicazioni.idricercatorefk = ricercatori.idricercatore
WHERE ricercatori.idUOFK =16
AND idricercatorefk IS NOT NULL 
AND (
idpubblicazionefk, posizione
)
IN (

SELECT idpubblicazionefk, MAX( posizione ) AS  'test'
FROM autoripubblicazioni
INNER JOIN pubblicazioni ON autoripubblicazioni.idpubblicazionefk = pubblicazioni.idpubblicazione
WHERE autoripubblicazioni.appendice =  'N'
AND pubblicazioni.anno =2007
GROUP BY idpubblicazionefk
HAVING test >2
)

[sonic85]

Originariamente inviato da cdimauro

C

Nello specifico il mio consiglio è di far diventare la subquery una derived-table, e quindi effettuarci una JOIN coi campi interessati. Funzionalmente è la stessa cosa, ma in termini prestazionali siamo un po' di parsec avanti.

Scusa, come si fa???