[MySql] DISPERAZIONE Mi aiutate a capire come ottimizzare questa query?

[Rintrah84]

Ragazzi,
tra una decina di giorni devo consegnare un progetto per un esame...praticamente devo ottimizzare un db inserendo indici ed ottimizzando le query...mi stò un po' impiccando e vi chiedo un aiuto.

Ho qualche idea ma un po' confusa...vediamo di cavarne qualcosa...per favore...

Consideriamo queste 3 tabelle InnoDb del mio database:

1) INSERZIONE (che contiene gli oggetti messi in vendita dagli utenti):

+-----------------+-----------------------+------+-----+---------+----------------+
| Field           | Type                  | Null | Key | Default | Extra          |
+-----------------+-----------------------+------+-----+---------+----------------+
| Id_Oggetto      | int(11)               | NO   | PRI | NULL    | auto_increment |
| Titolo          | varchar(60)           | NO   |     | NULL    |                |
| Descrizione     | text                  | YES  |     | NULL    |                |
| Costo_Base      | decimal(6,2)          | YES  |     | NULL    |                |
| Compralo_Subito | decimal(6,2)          | YES  |     | NULL    |                |
| Scadenza        | datetime              | YES  |     | NULL    |                |
| Stato_Oggetto   | enum('nuovo','usato') | YES  |     | NULL    |                |
| Id_Venditore    | varchar(20)           | NO   |     | NULL    |                |
| Id_Categoria    | varchar(30)           | NO   |     | NULL    |                |
+-----------------+-----------------------+------+-----+---------+----------------+
9 rows in set (0.04 sec)

2) UTENTE (che contiene gli utenti del sistema):

mysql> describe utente;
+--------------+--------------+------+-----+---------+-------+
| Field        | Type         | Null | Key | Default | Extra |
+--------------+--------------+------+-----+---------+-------+
| User_Id      | varchar(20)  | NO   | PRI | NULL    |       |
| Nome         | varchar(20)  | NO   |     | NULL    |       |
| Cognome      | varchar(20)  | NO   |     | NULL    |       |
| Data_Nascita | date         | NO   |     | NULL    |       |
| Indirizzo    | varchar(50)  | NO   |     | NULL    |       |
| Cap          | varchar(255) | NO   |     | NULL    |       |
+--------------+--------------+------+-----+---------+-------+
6 rows in set (0.05 sec)

3) COMUNI: che contiene la lista dei comuni italiani:

mysql> describe comuni;
+------------------+--------------+------+-----+---------+----------------+
| Field            | Type         | Null | Key | Default | Extra          |
+------------------+--------------+------+-----+---------+----------------+
| IdComune         | int(11)      | NO   | PRI | NULL    | auto_increment |
| comune           | varchar(255) | YES  |     | NULL    |                |
| provincia        | varchar(255) | YES  |     | NULL    |                |
| cap              | varchar(255) | YES  |     | NULL    |                |
| prefisso_tel     | varchar(255) | YES  |     | NULL    |                |
| codice_istat     | varchar(255) | YES  |     | NULL    |                |
| codice_catastale | varchar(255) | YES  |     | NULL    |                |
| sito_comune      | varchar(255) | YES  |     | NULL    |                |
+------------------+--------------+------+-----+---------+----------------+
8 rows in set (0.00 sec)

La tabella INSERZIONE contiene la bellezza di 548820 record (548820 oggetti messi in vendita)

La tabella UTENTE contiene 25011 utenti

La tabella COMUNI contiene gli 8101 comuni italiani

Quindi ho una prima tabella estremamente popolata, una seconda tabella che contiene un bel numero di record ma comunque molti meno della prima e la terza tabella che contiene relativamente pochi record.

Ora consideriamo questa query che dato il titolo testuale di un'inserzione restituisce il nome del comune di residenza dell'utente in questione (è una query abbastanza stupida e viene eseguita facendo un paio di join: il primo JOIN viene eseguito tra la tabella INSERZIONE e la tabella UTENTE e poi il secondo tra la tabella UTENTE e la tabella COMUNI per ricavare il nome del comune).

Data l'ENORME popolosità della tabella INSERZIONE eseguendola ci mette un sacco di tempo

SELECT comuni.comune
FROM inserzione, utente, comuni
WHERE inserzione.Titolo = 'OGGETTO WnYEnnYDnnn'
AND inserzione.Id_Venditore = utente.User_Id
AND utente.cap = comuni.cap;

Ci mette la bellezza di 1,25 minuti
Eccone la prova:

mysql> SELECT comuni.comune
    -> FROM inserzione, utente, comuni
    -> WHERE inserzione.Titolo = 'OGGETTO WnYEnnYDnnn'
    -> AND inserzione.Id_Venditore = utente.User_Id
    -> AND utente.cap = comuni.cap;
+--------+
| comune |
+--------+
| Caluso |
+--------+
1 row in set (1 min 25.35 sec)

Sulle slide della proff ho trovato il riferimento al NESTED LOOP JOIN che dovrebbe essere una strategia per ottimizzare query di questo tipo (almeno credo):

praticamente da quello che ho capito quando devo effettuare un Join tra una tabella molto grossa ed una più piccola posso mettere un indice selettivo su di una colonna utilizzata nella condizione di JOIN e questa tabella viene scelta come inner table che viene acceduta proprio tramite questo indice selettivo. L'altra invece è l'outer table e viene acceduta in maniera full table scan.

Quindi, in riferimento alla mia query di prima, mi viene da pensare che per ottimizzarla (ditemi se è una cavolta) che
potrei fare così:

1) Creo un indice sul campo Titolo della tabella INSERZIONE (a cui ora potrei accedere tramite indiceselettivo) per poter trovare più velocemente l'inserzione avente titolo 'OGGETTO WnYEnnYDnnn' (ad esempio).

2) Creo un altro indice sul campo User_Id della tabella UTENTE che diventa così l'INNER TABLE

E poi visto che la tabella COMUNI è relativamente piccola pensavo di fregarmene per ora.

Così facendo penso che succede circa questo: tramite l'indice sul campo Titolo di INSERZIONE filtro più velocemente per trovare il record che contiene l'oggetto in vendita con titolo 'OGGETTO WnYEnnYDnnn'. A questo punto devo fare il JOIN di INSERZIONE con UTENTE in base ai campi Id_Venditore della prima ed User_Id della seconda.

Id_Venditore ce l'ho perchè è quello relativo alla riga appena trovata, devo matchare in UTENTE e se ho un indice su User_ID accedo a questa tabella in maniera indicizzata e faccio prima

Potrebbe essere una strategia? Vi prego di aiutarmi...è straimportante.

Grazie
Andrea

[brasio]

Dovresti creare questi indici:

inserzione.Titolo
inserzione.Id_Venditore
utente.User_Id (è la chiave primaria e dovrebbe essere già inidicizzata)
utente.cap
comuni.cap

La tua query dovrebbe essere:

SELECT comuni.comune
FROM inserzione inner join utente
on inserzione.Id_Venditore = utente.User_Id
inner join comuni
on utente.cap = comuni.cap
WHERE inserzione.Titolo = 'OGGETTO WnYEnnYDnnn'

In realtà la where dovrebbe essere l'ultima condizione elaborata, per cui per cercare di fargli filtrare meno record possibili, dato che siamo in una inner join, potresti mettere la condizione di where dentro la join stessa:

SELECT comuni.comune
FROM inserzione inner join utente
on inserzione.Id_Venditore = utente.User_Id
and inserzione.Titolo = 'OGGETTO WnYEnnYDnnn'
inner join comuni
on utente.cap = comuni.cap

In realtà quest'ultimo punto dovrebbe essere inutile date le ottimizzazione insite nel motore del database.

[Rintrah84]

Originariamente inviato da brasio

Dovresti creare questi indici:

inserzione.Titolo
inserzione.Id_Venditore
utente.User_Id (è la chiave primaria e dovrebbe essere già inidicizzata)
utente.cap
comuni.cap

La tua query dovrebbe essere:

SELECT comuni.comune
FROM inserzione inner join utente
on inserzione.Id_Venditore = utente.User_Id
inner join comuni
on utente.cap = comuni.cap
WHERE inserzione.Titolo = 'OGGETTO WnYEnnYDnnn'

In realtà la where dovrebbe essere l'ultima condizione elaborata, per cui per cercare di fargli filtrare meno record possibili, dato che siamo in una inner join, potresti mettere la condizione di where dentro la join stessa:

SELECT comuni.comune
FROM inserzione inner join utente
on inserzione.Id_Venditore = utente.User_Id
and inserzione.Titolo = 'OGGETTO WnYEnnYDnnn'
inner join comuni
on utente.cap = comuni.cap

In realtà quest'ultimo punto dovrebbe essere inutile date le ottimizzazione insite nel motore del database.

Intanto inizio con il ringraziarti per la disponibilità nell'aver letto il mio non proprio corto 3d....

Allora mentre attendevo una risposta ho pensato di inserire un indice sul campo Titolo della tabella INSERZIONE (a cui ora potrei accedere tramite indice selettivo) per poter trovare più velocemente l'inserzione avente titolo 'OGGETTO WnYEnnYDnnn' (ad esempio), così trovo il record che mi interessa ed il relativo campo Id_Venditore....

Facendo solo questa cosa il tempo di esecuzione della query si è abbassato notevolmente passando da 1,25 minuti a 0,94 secondi

Dimmi se ho capito bene: facendo questa cosa accedo in modo indicizzato al record che mi interessa in INSERZIONE ed ho il ed il relativo campo Id_Venditore.

A questo punto eseguendo il JOIN tra INSERZIONE ed UTENTE il campo Id_Venditore viene fatto matchare con il campo User_Id della tabella UTENTE che in tale tabella è primary key che è un particolare tipo di indice, quindi la tabella UTENTE è come se fosse l'inner table e vi accedo mediante un indice selettivo realizzando così un nested loop join (anche se non ho dovuto mettere ulteriori indici in quanto il campo User_Id è primary key per UTENTE)

Ci può stare come ragionamento?

Per quanto riguarda l'ottimizzazione della struttura della query sò che è una cosa che piace alla professoressa....purtroppo non conosco molto bene la sintassi che hai usato te (sono abitutato ad usare l'altra...)

Dimmi se ho capito quello che fà

SELECT comuni.comune
FROM inserzione inner join utente
on inserzione.Id_Venditore = utente.User_Id
inner join comuni
on utente.cap = comuni.cap
WHERE inserzione.Titolo = 'OGGETTO WnYEnnYDnnn'

Per prima cosa gli dice cosa deve selezionare ovvero il campo comune della tabella COMUNI...OK

Poi mi sfugge cosa fa di preciso l'INNER JOIN (che fa?)

Perchè in questo caso se metto la where alla fine gli faccio filtrare meno record possibili?

Daidai queste informazioni sono oro per me....non sia mai che mi accettano il progetto e passo l'esame hahha

[gugoXX]

Si', confermo che per questa query e' sufficiente il solo indice sul titolo dell'inserzione.


INNER JOIN e JOIN sono sinonimi, e ti conviene sapere cosa e' una INNER JOIN per l'esame e la sua differenza con le OUTER JOIN

Il ragionamento che hai fatto sui filtri e sui loop e' corretto, ma
user_id della tabella utenti dovrebbe gia' avere un indice proprio perche' e' chiave primaria, quindi non dovresti rifarne un altro (penso addirittura che tu non possa proprio). Idem per idcoumune della tabella comuni.

La posizione della WHERE al fondo della query e' ininfluente. Non puo' essere altrimenti nella sintassi SQL.
L'ottimizzatore, se le statistiche sono corrette, la eseguira' comunque come primo passo sulla tabella inserzioni per cercare le inserzioni implicate dal filtro.
SQL e' solo dichiarativo. Pensa che la SELECT della clausola, che e' quella che l'operatore scrive per prima, e' in realta' l'ultima operazione che viene eseguita dal motore...

Ah, attenzione, poiche' non hai un vincolo di unicita' sulla tabella inserzioni sul campo descrizione, significa che la tua query potrebbe restituire piu' record.
Se questo effetto e' indesiderato perche' tu "sai" che non possono esistere 2 descrizioni identiche per 2 inserzioni diverse, e magari un tuo applicativo e' stato disegnato considerando questa regola implicita, il non mettere il vincolo di unicita' e' considerato errore.

[brasio]

Non c'è modo di prevedere quello che il motore sql fa se non eseguendo la tua query con "Explain" che la precede. Otterrai tutte le informazioni che ti servono, utilizzo degli indici compreso. Mi sembra che in MYSQL si possa scrivere anche "explain extended" che dà informazioni aggiuntive, non so se addirittura suggerimenti e warning.
Sul fatto che tutte le chiavi primarie siano automaticamente indicizzate è vero: lì ho usato il condizionale perchè non sono sicuro che versioni molto vecchie di database (non siamo quindi nel tuo caso) lo facciano automaticamente (sarebbe facile comunque verificarlo).
Sul fatto che il campo cap debba o no essere indicizzato, lo puoi vedere con il piano di esecuzione di cui sopra (explain).

In linea generale devo fare due osservazioni sul disegno delle tue tabelle:

1) il campo userid e idvenditore sono varchar(20): sarebbe meglio utilizzare degli int dato che si ottengono performance migliori per gli indici nel seguente ordine: numerici interi, caratteri a lunghezza fissa (char), caratteri a lunghezza variabile (varchar, il tuo caso)

2) lo stesso vale per il campo cap, che tra l'altro è sovradimensionato. Il campo CAP potrebbe anche essere la chiave primaria della tua tabella se la corrispondenza "comune cap" fosse univoca.

La posizione della where, come ti ho scritto anche io, è ininfluente, grazie al motore del database che fa tutte le ottimizzazioni (a rigore, se non ci fossero per assurdo le ottimizzazioni, la where sarebbe elaborato dopo il prodotto cartesiano della join e quindi sarebbe uno svantaggio dato che dovrebbe filtrare su un numero di record maggiori)

[Rintrah84]

Originariamente inviato da gugoXX

Si', confermo che per questa query e' sufficiente il solo indice sul titolo dell'inserzione.


INNER JOIN e JOIN sono sinonimi, e ti conviene sapere cosa e' una INNER JOIN per l'esame e la sua differenza con le OUTER JOIN

Il ragionamento che hai fatto sui filtri e sui loop e' corretto, ma
user_id della tabella utenti dovrebbe gia' avere un indice proprio perche' e' chiave primaria, quindi non dovresti rifarne un altro (penso addirittura che tu non possa proprio). Idem per idcoumune della tabella comuni.

La posizione della WHERE al fondo della query e' ininfluente. Non puo' essere altrimenti nella sintassi SQL.
L'ottimizzatore, se le statistiche sono corrette, la eseguira' comunque come primo passo sulla tabella inserzioni per cercare le inserzioni implicate dal filtro.
SQL e' solo dichiarativo. Pensa che la SELECT della clausola, che e' quella che l'operatore scrive per prima, e' in realta' l'ultima operazione che viene eseguita dal motore...

Ah, attenzione, poiche' non hai un vincolo di unicita' sulla tabella inserzioni sul campo descrizione, significa che la tua query potrebbe restituire piu' record.
Se questo effetto e' indesiderato perche' tu "sai" che non possono esistere 2 descrizioni identiche per 2 inserzioni diverse, e magari un tuo applicativo e' stato disegnato considerando questa regola implicita, il non mettere il vincolo di unicita' e' considerato errore.

Sisi la differenza tra INNER JOIN ed OUTER JOIN (left, right e full) me la sono appena andata a ripassare sui vecchi appunti...era un anno e mezzo che non mettevo mano su un DB...

Per quanto riguarda il vincolo di unicità del campo Titolo no problema e lo avevo previsto e voluto...è un sistema di aste online e voglio che possano essere inseriti articoli aventi lo stesso titolo come ad esempio: 'Macchina fotografica digitale Nikon D90' sarebbe un casino se gli utenti dovessero mettere titoli per forza diversi...tanto la chiave di quella tabella è un altra quindi no problem.

Ti ringrazio

[Rintrah84]

Originariamente inviato da brasio

Non c'è modo di prevedere quello che il motore sql fa se non eseguendo la tua query con "Explain" che la precede. Otterrai tutte le informazioni che ti servono, utilizzo degli indici compreso. Mi sembra che in MYSQL si possa scrivere anche "explain extended" che dà informazioni aggiuntive, non so se addirittura suggerimenti e warning.
Sul fatto che tutte le chiavi primarie siano automaticamente indicizzate è vero: lì ho usato il condizionale perchè non sono sicuro che versioni molto vecchie di database (non siamo quindi nel tuo caso) lo facciano automaticamente (sarebbe facile comunque verificarlo).
Sul fatto che il campo cap debba o no essere indicizzato, lo puoi vedere con il piano di esecuzione di cui sopra (explain).

In linea generale devo fare due osservazioni sul disegno delle tue tabelle:

1) il campo userid e idvenditore sono varchar(20): sarebbe meglio utilizzare degli int dato che si ottengono performance migliori per gli indici nel seguente ordine: numerici interi, caratteri a lunghezza fissa (char), caratteri a lunghezza variabile (varchar, il tuo caso)

2) lo stesso vale per il campo cap, che tra l'altro è sovradimensionato. Il campo CAP potrebbe anche essere la chiave primaria della tua tabella se la corrispondenza "comune cap" fosse univoca.

La posizione della where, come ti ho scritto anche io, è ininfluente, grazie al motore del database che fa tutte le ottimizzazioni (a rigore, se non ci fossero per assurdo le ottimizzazioni, la where sarebbe elaborato dopo il prodotto cartesiano della join e quindi sarebbe uno svantaggio dato che dovrebbe filtrare su un numero di record maggiori)

Quindi mi stai dicendo che tra questa query:

SELECT comuni.comune
FROM inserzione inner join utente
on inserzione.Id_Venditore = utente.User_Id
inner join comuni
on utente.cap = comuni.cap
WHERE inserzione.Titolo = 'OGGETTO WnYEnnYDnnn'

e questa:

SELECT comuni.comune
FROM inserzione inner join utente
on inserzione.Id_Venditore = utente.User_Id
and inserzione.Titolo = 'OGGETTO WnYEnnYDnnn'
inner join comuni
on utente.cap = comuni.cap

Non vi è differenza?

Ed invece c'è differenza rispetto alla mia originale?

SELECT comuni.comune
FROM inserzione, utente, comuni
WHERE inserzione.Titolo = 'OGGETTO WnYEnnYDnnn'
AND inserzione.Id_Venditore = utente.User_Id
AND utente.cap = comuni.cap;

che non mette il where alla fine?
mmm anche se l'ottimizzatore fa molte scelte la proff è fissata che vuole che ottimizziamo noi le query perchè dice che l'ottimizzatore può sbagliare....

La differenza con la tua è che mentre nella tua fa un super inner join nella mia filtra ben 3 clausole WHERE...ho capito bene?

Grazie
Andrea

[brasio]

sono tutte e tre identiche.
Sul fatto che l'ottimizzatore di prodotti così navigati come mysql si possa sbagliare per semplici query di questo tipo ho seri dubbi.

[Rintrah84]

Originariamente inviato da brasio

sono tutte e tre identiche.
Sul fatto che l'ottimizzatore di prodotti così navigati come mysql si possa sbagliare per semplici query di questo tipo ho seri dubbi.

anche io...però è una esplicita richiesta della proff per poter consegnare il progetto DOH !!! Quindi le dovrò fare comunque...che bello fare cose inutili...

Cmq dici che con Explain posso dimostrargli che il piano di esecuzione è lo stesso?

[brasio]

puoi verificarlo subito con explain.

Tra l'altro quello che dice la tua prof. mi suona strano: come pensa di poter scrivere delle query su mysql che non passino per l'ottimizzatore e che non usino le statistiche interne?
Forse su mysql c'è qualche opzione che permette di forzare l'utilizzo di indici a mia scelta? Su questo non ti so rispondere, dovresti cercare su google o su qualche manuale specifico di mysql

[shinya]

Originariamente inviato da Rintrah84

mmm anche se l'ottimizzatore fa molte scelte la proff è fissata che vuole che ottimizziamo noi le query perchè dice che l'ottimizzatore può sbagliare....

Questa è una delle affermazioni più assurde e pericolose che io abbia mai sentito. Su Oracle è possibile forzare un piano d'esecuzione rispetto ad un altro, dirgli di usare un indice o no, di usare l'ottimizzatore a regole piuttosto che quello a costi, ma sono meccanismi che servono solo per l'analisi dei possibili piani d'esecuzione e il tuning delle performance, non per scrivere codice in produzione.

Questo mi sembra il classico premature optimization. Forse la tua prof. ignora che, ad esempio, a volte un full table scan sia più veloce di una scansione per indice... (tabelle piccole, indici poco selettivi... una full table scan legge a blocchi dal disco, una index scan non sempre).

La sostanza è: lasciate che l'ottimizzatore faccia il suo lavoro!

[gugoXX]

Originariamente inviato da shinya

La sostanza è: lasciate che l'ottimizzatore faccia il suo lavoro!

Quoto.
Soprattutto perche' magari col tempo le densita' e le statistiche di una tabella cambiano, e i percorsi forzati con gli Hint potrebbero risultare poi peggiori di quelli dedotti dal motore.

[brasio]

Forse la tua prof. si riferisce al fatto che esistono vecchi database embedded di piccole dimensioni, senza un vero motore di ottimizzazione, utilizzati da dispositivi mobili. In essi è necessario ottimizzare manualmente le query, ma diventa un lavoro a parte, che esula da un normale corso di sql.

Per esempio in questi dispositivi, una semplice query del tipo

select * from miatabella where miocampo='ciccio' or miocampo='tizio'

potrebbe essere più veloce se scritta come

select * from miatabella
where miocampo='ciccio'
UNION
select * from miatabella
where miocampo='tizio'

Ma ti ripeto, è un settore a parte che non ha molto senso con quanto afferma la tua prof. e con gli strumenti che vi fa usare

[Rintrah84]

eh tante cose mi suonano strane pure a me di sto corso...come la storia di inrementare le performance delle query usando le view...però uno deve anche riuscire a mediare tra fare le cose fatte bene ed inserirci dentro quelle 3 stronzate inutili che piacciono tanto al docente...DOH

[d_traveler]

scusate, ma ste cose dove posso studiarle? ne vedo parlare spesso in molti topic ed in molti forum.

[brasio]

Uno in particolare che racchiuda tutto non saprei consigliartelo.
Esistono libri su SQL avanzato in generale molto buoni come

"The Art of SQL"
"SQL Performance Tuning"
"Joe Celko's SQL for Smarties: Advanced SQL Programming"

e molti altri, anche più vecchi, ma non ricordo i titoli.

Poi ci sono tutta una serie di libri specifici per un db. Ad esempio per sql server 2008 c'è l'ottimo:

"SQL Server 2008 Query Performance Tuning Distilled"

[shinya]

Originariamente inviato da d_traveler

scusate, ma ste cose dove posso studiarle? ne vedo parlare spesso in molti topic ed in molti forum.

Al di là dell'ABC... conviene leggere direttamente la documentazione del tuo db.

[Rintrah84]

Originariamente inviato da d_traveler

scusate, ma ste cose dove posso studiarle? ne vedo parlare spesso in molti topic ed in molti forum.

Per MySql tu consiglio: High Performance MySQL (2008, Schwartz Zaitsev Tkachenko Zawodny Lentz and Balling, O'Reilly

E' in inglese ma è un inglese molto comprensibile e pare fatto bene...ci stò studiando in questi giorni...lo "compri" facilmente online