• D
    User Attivo •

    mySql Lentissimo, mySql Rapidissimo

    Ciao a tutti,
    ho bisogno di un po di conforto, sono disperato....
    Vi spiego, ho realizzato un sito con un database abnorme, qualcosina tipo 200-300MB ( e sono solo dati testuali ), il fatto è che il sito di test era sotto un server di una ditta di hosting, e il sito vero e proprio è sotto un altro hosting.
    Il problema: una query abbastanza corposa mi impiega 54 Secondi nel server ufficiale :gtsad:
    Mentre in quello che solitamente usavo per i test appena 1.5 secondi....
    E ovviamente il database è identico ( importazione fatta da pochi giorni) e identici sono indici e chiavi e tutto quel che ne consegue.....
    I test dei tempi li ho fatti tramite il pannello di phpMyAdmin...
    Come diavolo posso fare? :crying:

    0
  • T
    ModSenior •

    Ciao develUp,

    incolla la query, e vediamo se è migliorabile...

    0
  • D
    User Attivo •

    Ecchela:

    SELECT          
testataCalendario.dataPartitta,         
testataCalendario.campionato,         
righeCalendario.societa1,         
righeCalendario.societa2,         
righeCalendario.goal1,         
righeCalendario.goal2,         
righeFormazioni.sostAvuta, 
righeFormazioni.sostFatta,         
righeFormazioni.ammonito, 
righeFormazioni.espulso,         
righeFormazioni.posizione,         
righeFormazioni.partita,         
righeFormazioni.giornata,         
societa.societa as nomeSocietaGiocatore, 
societa.id as idSocietaGiocatore, 
count(righeGoal.id) as quantiGoal         

FROM testataCalendario, righeCalendario, societa,         
righeFormazioni LEFT OUTER JOIN righeGoal 
ON righeGoal.partita = righeFormazioni.partita 
AND righeGoal.persone = righeFormazioni.persone              
AND righeGoal.stagione = righeFormazioni.stagione 

WHERE righeFormazioni.stagione = '2009-10'          
AND righeFormazioni.persone = 'BUFFON Gianluigi' 
AND righeFormazioni.stagione = righeCalendario.stagione          
AND righeFormazioni.partita = righeCalendario.id
AND testataCalendario.stagione = righeFormazioni.stagione          
AND societa.id = righeFormazioni.societa         
AND testataCalendario.id = righeCalendario.giornata         
AND ( righeFormazioni.posizione < 12 OR righeFormazioni.sostFatta  != '0') 

GROUP BY righeFormazioni.id 
ORDER BY testataCalendario.numeroGiornata DESC
    0
  • T
    ModSenior •

    Che dimensioni hanno le 2 tabelle?

    0
  • D
    User Attivo •

    In che senso 2 tabelle?
    Nella query ne vengono coinvolte 5...
    Comunque:
    testataCalendario: 2.907 inserimenti
    righeCalendario: 26.275 ins.
    societa: 900
    righeFormazioni: 557.891
    righeGoal: 37.547

    O forse ho capito male cosa mi stavi chiedendo?

    0
  • M
    User •

    @develUp said:

    E ovviamente il database è identico ( importazione fatta da pochi giorni) e identici sono indici e chiavi e tutto quel che ne consegue.....
    I test dei tempi li ho fatti tramite il pannello di phpMyAdmin...
    Come diavolo posso fare? :crying:

    Sembra un problema di indici, riesci a postare uno show create table di tutte le tabelle coinvolte?
    Per sapere quali indici vengono utilizzati e se vengono utilizzati riesegui la tua query utilizzando explain

    
EXPLAIN SELECT          
testataCalendario.dataPartitta,         
......

    e posta i risultati, così possiamo aiutarti.
    Ciao

    0
  • D
    User Attivo •

    Per quanto riguarda l'EXPLAIN
    www.giorgiotave.it/media/images/sharingImages/1905.jpg

    Di seguito gli show create:

    righeFormazioni
    CREATE TABLE righeFormazioni (\n id int(11) NOT NULL auto_increment,\n giornata int(11) NOT NULL,\n partita int(11) NOT NULL,\n stagione varchar(255) NOT NULL,\n societa varchar(255) NOT NULL,\n posizione int(11) NOT NULL,\n persone varchar(255) NOT NULL,\n sostAvuta int(11) NOT NULL COMMENT 'esce',\n sostFatta int(11) NOT NULL COMMENT 'entra',\n ammonito int(11) NOT NULL,\n espulso int(11) NOT NULL,\n voto double NOT NULL,\n dataAgg int(11) NOT NULL,\n PRIMARY KEY (id),\n UNIQUE KEY giornata (giornata,partita,societa,posizione,stagione)\n) ENGINE=MyISAM DEFAULT CHARSET=latin1

    testataCalendario
    CREATE TABLE testataCalendario (\n idUnivoco int(11) NOT NULL auto_increment,\n dataPartitta varchar(255) NOT NULL,\n campionato varchar(255) NOT NULL,\n id int(11) NOT NULL COMMENT 'TESTATA,ID è collegata direttamente a RIGHECALENDARIO.giornata',\n numeroGiornata int(11) NOT NULL,\n stagione varchar(255) NOT NULL,\n dataAgg varchar(255) NOT NULL,\n PRIMARY KEY (idUnivoco),\n UNIQUE KEY campionato (campionato,id,numeroGiornata,stagione)\n) ENGINE=MyISAM DEFAULT CHARSET=latin1

    righeCalendario
    CREATE TABLE righeCalendario (\n idUnivoco int(255) NOT NULL auto_increment,\n nazione varchar(15) NOT NULL,\n campionatoCalendario varchar(100) NOT NULL,\n giornata int(11) NOT NULL,\n id int(11) NOT NULL,\n societa1 varchar(255) NOT NULL,\n societa2 varchar(255) NOT NULL,\n goal1 int(11) NOT NULL,\n goal2 int(11) NOT NULL,\n arbitro varchar(255) NOT NULL,\n allenatore1 varchar(255) NOT NULL,\n allenatore2 varchar(255) NOT NULL,\n modulo1 varchar(255) NOT NULL,\n modulo2 varchar(255) NOT NULL,\n dataAgg varchar(255) NOT NULL,\n dataPartita varchar(255) NOT NULL,\n sospesa varchar(255) NOT NULL,\n aggiornata varchar(255) NOT NULL,\n stagione varchar(255) NOT NULL default '2008-09',\n PRIMARY KEY (idUnivoco),\n UNIQUE KEY giornata (giornata,societa1,societa2,stagione)\n) ENGINE=MyISAM DEFAULT CHARSET=latin1

    societa
    CREATE TABLE societa (\n id varchar(20) NOT NULL,\n societa varchar(255) NOT NULL,\n denominazione varchar(255) NOT NULL,\n nazione varchar(15) NOT NULL,\n indirizzo varchar(255) NOT NULL,\n citta varchar(255) NOT NULL,\n telefono varchar(255) NOT NULL,\n fax varchar(255) NOT NULL,\n stadio varchar(255) NOT NULL,\n email varchar(255) NOT NULL,\n sitoWeb varchar(255) NOT NULL,\n coloriSociali varchar(255) NOT NULL,\n note varchar(255) NOT NULL,\n matricola int(11) NOT NULL,\n allenatore varchar(255) NOT NULL,\n allenatoreP varchar(255) NOT NULL,\n rigaAllenatore varchar(255) NOT NULL,\n immagine varchar(255) default NULL,\n PRIMARY KEY (id)\n) ENGINE=MyISAM DEFAULT CHARSET=latin1

    righeGoal
    CREATE TABLE righeGoal (\n id int(11) NOT NULL auto_increment,\n giornata int(11) NOT NULL,\n partita int(11) NOT NULL,\n stagione varchar(100) NOT NULL default '2008-09',\n societa varchar(255) NOT NULL,\n posizione int(11) NOT NULL,\n persone varchar(255) NOT NULL,\n tipo varchar(255) NOT NULL,\n minuto int(11) NOT NULL,\n dataAgg varchar(255) NOT NULL,\n PRIMARY KEY (id),\n UNIQUE KEY persone (persone,giornata,stagione,minuto)\n) ENGINE=MyISAM DEFAULT CHARSET=latin1

    GRAZIE di cuore a tutti 🙂

    0
  • M
    User •

    @develUp said:

    Per quanto riguarda l'EXPLAIN
    http://www.giorgiotave.it/media/imag...mages/1905.jpg

    Ciao,
    ti confermo che è un problema di indici e di chiavi, in questo caso la ricerca nella tabella righeFormazioni è fatta sequenzialmente per 557891 record ed è molto lenta come anche nella tabella testataCalendario per molti meno record.

    Per cominciare ti consiglio di creare un unico index nella tabella righeFormazioni che comprenda in questo ordine:
    persone
    stagione
    partita

    vedrai che ci metterà un po' di tempo per crearlo ma poi la query sara un po' più veloce.
    Fai questa modifica e poi mandami un altro explain che controlliamo che sia tutto ok e ottimizziamo ulteriormente.

    Ciao
    Matteo

    0
  • D
    User Attivo •

    Ciao Matteo grazie 1.000.000
    Allora, mi sono letto qualche guida sugli indici e prima che tu mi rispondessi ne ho provati a creare qualcuno, e, strano ma vero avevo ottimizzato parecchio:
    rows di righeFormazioni da 557.891 erano arrivate a 70.000 circa,
    ho inserito l'indice come mi hai suggerito te, e sono adesso scese a 27!!!
    ( da notare che ti ho dedicato il nome dell indice 🙂 )

    Immagine dell explain:
    http://www.giorgiotave.it/media/images/sharingImages/1906.jpg

    Mi spiegheresti in base a quali criteri hai scelto persone,stagione,partita?
    Così almeno se mi dovessi ritrovare in una situazione analoga mi sbroglio da solo

    0
  • M
    User •

    @develUp said:

    ( da notare che ti ho dedicato il nome dell indice 🙂 )

    Grazie mille, ne sono onorato, ma ti conviene utilizzare un nome più "self explaing" tipo key_persone_stagione_partita

    @develUp said:

    Mi spiegheresti in base a quali criteri hai scelto persone,stagione,partita?

    E' semplicemente l'ordine della clausola where della query che fai a quella tabella.
    Esercizio: c'è un'altra tabella ottimizzabile, trovala e scrivi l'indice corretto 😉

    Domanda: il tempo di esecuzione della query di quanto è ora?

    Ciao
    Matteo

    0
  • D
    User Attivo •

    E' di 0.0240 sec 🙂
    Domanda: come mai non si è tenuto conto di questa clausola:
    AND ( righeFormazioni.posizione < 12 OR righeFormazioni.sostFatta != '0') ?

    La prossima direi che è proprio righeCalendario e nell'indice metterei:
    stagione, id ,giornata... c ho preso?

    Altra domanda, perchè abbiamo utilizzato INDEX anzichè UNIQUE?
    Credevo, ( a quanto pare erroneamente ) che se dichiarassi un indice come unico avrei risparmiato fatica al povero server....

    0
  • M
    User •

    @develUp said:

    E' di 0.0240 sec 🙂
    Domanda: come mai non si è tenuto conto di questa clausola:
    AND ( righeFormazioni.posizione < 12 OR righeFormazioni.sostFatta != '0') ?

    perchè non l'avevo vista, aggiungila all'index e guarda cosa succede.

    @develUp said:

    La prossima direi che è proprio righeCalendario e nell'indice metterei:
    stagione, id ,giornata... c ho preso?

    Se non mi è scappata un altra condizione direi che: sì, ci hai preso.

    @develUp said:

    Altra domanda, perchè abbiamo utilizzato INDEX anzichè UNIQUE?
    Credevo, ( a quanto pare erroneamente ) che se dichiarassi un indice come unico avrei risparmiato fatica al povero server....
    Non è l'indice ad essere unico ma ciò che è indicizzato in questo caso deve essere unica la tripletta stagione, id ,giornata pena un errore in fase di inserimento.
    Gli indici unici servono per mantenere coerenza dei dati.

    Ciao
    Matteo

    0
  • A
    User Attivo •

    Questa storia degli indici, spesso e volentieri mi ha lasciato molto stupito.

    In breve, come funzionano e in base a cosa si impostano?

    0
  • M
    User •

    @Amistat said:

    In breve, come funzionano e in base a cosa si impostano?

    In sintesi, gli indici permettono a mysql di cercare il dato che gli serve senza doversi scorrere la tabella riga per riga.
    Non sono un DBA quindi non conosco il funzionamento a basso livello, quello che so è che utilizzando gli indici mysql accede alla tabella in modo casuale e non sequenziale accorciando di molto il tempo delle query.

    Si impostano in base ai parametri di ricerca delle query, la regola è che serve un indice ogni gruppo di richieste, nel caso sotto l'indice per righeCalendario è composto da stagione, id ,giornata perché quella query utilizza quei campi in quell'ordine.

    Occhio a non esagerare perché ogni inserimento mysql aggiorna gli indici e se ce ne sono troppi il tempo di inserimento lievita.

    Spero di averti aiutato.
    Matteo

    0
  • A
    User Attivo •

    Ma facendo un esempio su una query molto più semplice (anche se magari serve meno) come potrebbe essere?

    0
Effettua l'accesso per rispondere