QUERY Lentissima: motivi?

[sonic85]

Ciao a tutti,
ho un quesito da porvi... Sto costruendo un db in cui sono presenti delle pubblicazioni 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 + autori! !!
Come mai? cosa sbaglio? Consigli?

PS. Utilizzo MYSQL 5.0 con InnoDB

[nicola75ss]

Il problema è sicuramente nella clausola IN. Posta un dump delle tabelle comprensivo di create e di insert.

[Squid70]

1) La IN è sicuramente uno dei motivi.
Riscrivi questo pezzo (che è veramente orribile):

(idpubblicazionefk,posizione) IN
(SELECT idpubblicazionefk, MAX(posizione) as 'test'
FROM autoripubblicazioni
GROUP BY idpubblicazionefk)

usando la EXISTS

2) Quanti e quali indici hai definito sulle tabelle ???

[sonic85]

Ecco il dump! Il db è molto complesso in verità!
ci sn circa 15 tabelle: in questo caso sono interessate 4 Pubblicazioni(idpubblicazione), autoripubblicazioni(idautoripubblicazioni, idricercatoreFK, idpubblicazioneFK), ricercatori(idricercatore idUOFK).

Squid70: scusa l'ignoranza,basta solo che sostituisca I'IN con EXISTS??

[sonic85]

CREATE TABLE IF NOT EXISTS `pubblicazioni` ( `idPubblicazione` int(11) NOT NULL AUTO_INCREMENT, `titolo` varchar(500) NOT NULL, `anno` int(4) NOT NULL, `volume` varchar(20) DEFAULT NULL, `fascicolo` varchar(20) DEFAULT NULL, `pagInizio` int(11) DEFAULT NULL, `pagFine` int(11) DEFAULT NULL, `bancaDati` varchar(20) DEFAULT NULL,`idRivistaFK` int(11) DEFAULT NULL, PRIMARY KEY (`idPubblicazione`), KEY `idLineaK` (`idLineaFK`), KEY `idRivistaK` (`idRivistaFK`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=3143 ;

[sonic85]

Ecco il dump! Il db è molto complesso in verità!
ci sn circa 15 tabelle: in questo caso sono interessate 3:
1) Pubblicazioni(idpubblicazione),
2) autoripubblicazioni(idautoripubblicazioni, idricercatoreFK, idpubblicazioneFK),
3) ricercatori(idricercatore idUOFK).

[sonic85]

CREATE TABLE IF NOT EXISTS `autoripubblicazioni` (
`idAutoriPubblicazioni` int(11) NOT NULL AUTO_INCREMENT,
`posizione` int(11) NOT NULL,
`appendice` char(1) NOT NULL DEFAULT 'N',
`affiliazioneCorretta` char(1) DEFAULT NULL,
`AffErrata` varchar(90) DEFAULT NULL,
`autoreCorrisp` char(1) DEFAULT NULL,
`idRicercatoreFK` int(11) DEFAULT NULL,
`AutoreEsterno` varchar(50) DEFAULT NULL,
`idPubblicazioneFK` int(11) NOT NULL,
PRIMARY KEY (`idAutoriPubblicazioni`),
KEY `idRicercatoreK` (`idRicercatoreFK`),
KEY `idPubblicazioneK` (`idPubblicazioneFK`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=33757 ;

[sonic85]

CREATE TABLE IF NOT EXISTS `ricercatori` (
`idRicercatore` int(11) NOT NULL AUTO_INCREMENT,
`cognome` varchar(45) NOT NULL,
`nome` varchar(45) NOT NULL,
`sesso` char(1) DEFAULT NULL,
`idUOFK` int(11) DEFAULT NULL,
PRIMARY KEY (`idRicercatore`),
KEY `idUOFK` (`idUOFK`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=287 ;

[nicola75ss]

Elimina i dati sensibili e posta pure le insert con qualche record per tutte le tabelle coinvolte in maniera che chi vuole aiutarti non debba perdere tempo a fare data entry.

[Squid70]

Più o meno viene così:
SELECT
count(*) AS 'ultimo'
FROM
autoripubblicazioni AS AP
INNER JOIN ricercatori AS R
on AP.idricercatorefk = R.idricercatore
INNER JOIN pubblicazioni AS P
on AP.idpubblicazionefk = P.idpubblicazione
WHERE
AP.idricercatorefk IS NOT NULL
AND
R.idUOFK = 16
AND
anno = 2007
AND
appendice='N'
AND
EXISTS
(
SELECT 1 FROM (SELECT idpubblicazionefk, MAX(posizione) as 'test'
FROM autoripubblicazioni
GROUP BY idpubblicazionefk
HAVING test>2) AS APP
WHERE AP.idpubblicazionefk = APP.idpubblicazionefk
AND
AP.posizione = APP.posizione
)

.. rimane il mistero della tabella di appartenenza dai campi 'anno' ed 'appendice' ....