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

[AndreaNobili]

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

[mondobimbi]

prova così :
su inserzioni inserisci una chiave secondaria sul campo titolo ed una sul campo id-venditore e modifica la query in questa maniera

SELECT C.comune
FROM inserzione as I
JOIN utente as U
ON I.Id_Venditore = U.User_Id
JOIN comuni as C
ON U.cap = C.cap
WHERE I.Titolo = 'OGGETTO WnYEnnYDnnn'

[AndreaNobili]

Originariamente inviato da mondobimbi
prova così :
su inserzioni inserisci una chiave secondaria sul campo titolo ed una sul campo id-venditore e modifica la query in questa maniera

SELECT C.comune
FROM inserzione as I
JOIN utente as U
ON I.Id_Venditore = U.User_Id
JOIN comuni as C
ON U.cap = C.cap
WHERE I.Titolo = 'OGGETTO WnYEnnYDnnn'

Ti ringrazio ma ti devo fare qualche domanda:

1) Una chiave secondaria non è come mettere un indice? Nel senso le chiavi le ho scelte anche per motivi logici...non posso mettere un indice ed ottenere circa lo stesso effetto?

2) Mi puoi spiegare il codice della tua versione della query? Non uso mai questa sintassi e non la capisco bene...a livello logico cosa cambia dalla mia query?

Grazie mille
Andrea

[mondobimbi]

Originariamente inviato da AndreaNobili
Ti ringrazio ma ti devo fare qualche domanda:

1) Una chiave secondaria non è come mettere un indice? Nel senso le chiavi le ho scelte anche per motivi logici...non posso mettere un indice ed ottenere circa lo stesso effetto?

indice secondario
la sintassi è
ALTER TABLE inserzione ADD INDEX (titolo), ADD INDEX (id_venditore);

2) Mi puoi spiegare il codice della tua versione della query? Non uso mai questa sintassi e non la capisco bene...a livello logico cosa cambia dalla mia query?

Grazie mille
Andrea

dovrebbe essere la stessa cosa, mysql dovrebbe trasformarla al suo interno in un where tipo quello che hai scritto tu, ma chiarisce meglio le dipendenze

[AndreaNobili]

Originariamente inviato da mondobimbi
indice secondario
la sintassi è
ALTER TABLE inserzione ADD INDEX (titolo), ADD INDEX (id_venditore);

dovrebbe essere la stessa cosa, mysql dovrebbe trasformarla al suo interno in un where tipo quello che hai scritto tu, ma chiarisce meglio le dipendenze

mmm io ho inserito un indice (con una sintassi lievemente diversa trovata tra le guide di html.it).
Ora ci mette tipo 0,94 secondi invece di 1,25 minuti....

Mi pare veramente un bel vantaggio...spero che questo sia un'ottimizzazione considerata valida per il progetto...ora ho mandato una mail alla proff per chiedere conferma....

Mi sai dire qualcosa circa la correttezza del mio pippotto mentale circa il nested loop join di cui parlavo del primo post?

Grazie
Andrea

[mondobimbi]

forse dovresti dare una occhiata alla sintassi dei comandi
JOIN
INNER JOIN
OUTER JOIN
coi modificatori LEFT e RIGHT
perchè è a questo a cui si rifirisce il tuo prof
ciao
sergio