[Mysql] Query complessa one-to-many, many-tomany?

[forrestG]

Scusate ma è tutto il giorno che provo, qualcuno potrebbe consigliarmi qual è il modo più veloce per recuperare dei dati organizzati come segue:

tbl_contact   |  tbl_indirizzi   |  tbl_numeri     |  tbl_category
              |                  |                 |
id            |  value           |  value          |  id
nome          |  contact_id      |  contact_id     |  label
cognome       |  category_id     |  category_id    |  type
altro

In poche parole ho una lista di contatti in cui ogni contatto ha dei definiti nella tabella (nome cognome etc) ed altri dati che possono essere multipli memorizzati in alcune tabelle esterne:

un contatto può avere molti indirizzi o molti numeri, la cui associazione col contatto è data dalla foreign_key contact_id. Allo stesso modo un indirizzo/numero ha una categoria di appartenenza (casa, ufficio, cell) definita in un'altra tabella, legata al dato tramite una seconda foreign_key chiamata category_id.

Ora devo fare una lista dei contatti e mostrare tutti i dati legati ad esso quanti che siano.
L'idea è quella di creare una tabella in questo modo:

contatti.id   contatti.nome    datomultiplo.value   datomultiplo.label   datomultiplo.type

1             Paolo            Via Garibaldi        Casa                 Indirizzo
1             Paolo            Via Grandi           Ufficio              Indirizzo
1             Paolo            02020202             Casa                 Numero
2             Maria            Via Volta            Vacanze              Indirizzo
2             Maria            Via Roma             Ufficio              Indirizzo
2             Maria            32323232             Cell                 Numero

Le prime due colonne sono i dati della colonna "contact", la terza lista tutti i parametri di "indirizzi" o "numeri" che hanno contact_id = contact.id e dalla terza sono i dati della tabella categorie legati al rispetti indirizzo da category_id.
Una volta presa la tabella la pulisco in php (elimino i dati duplicati) per passarla allo script che renderizza la tabella.

Per farlo d'apprima ho pensato di usare più query da PHP ovvero:
1 - mi prendo tutti i contatti che devo far stare nella pagina
2 - ciclo il result set e faccio una query che mi recupera solo i dati del relativo id della riga

SELECT id, nome, cognome FROM contacts LIMIT 0, 20

e fin qui ok. Ma come metto insieme tutti i dati esterni organizzati come le ultime 3 colonne?
Primo dubbio sulla reale performance della query.

 SELECT value, contact_id, label, model
  FROM contacts, indirizzi
   LEFT JOIN categories 
   ON category_id = categories.id
  WHERE indirizzi.contact_id = 2
UNION
 SELECT value, contact_id, label, model
  FROM contacts, numeri
   LEFT JOIN categories 
   ON category_id = categories.id
  WHERE numeri.contact_id = 2

Così mi ritorna una tabella simile a quella mostrata sopra, ma senza tutti i dati duplicati del contatto E PER UN SOLO contatto. Ciclo in PHP e riorganizzo l'array.

Secondo dubbio: l'UNION è realmente l'unico metodo?
Terzo dubbio: per elencare 20 contatti devo eseguire 21 query consecutive. Non sarà forse un po' esagerato? Figuriamoci se nella pagina devo mostrarne 100.

Quindi ho optato per una query unica che mi ritornasse una tabella come quella sopra descritta.
Il problema è che probabilmente ho complicato tutto e combino un casino che è meglio farlo in Nmila richieste:

SELECT contact.id, contact.nome, contact.cognome, ext.value, ext.label, ext.model
FROM contact, (
  SELECT value, contact_id, label, model
    FROM indirizzi
     LEFT JOIN categories 
     ON category_id = categories.id
  UNION
   SELECT value, contact_id, label, model
    FROM numeri
     LEFT JOIN categories 
     ON category_id = categories.id
  ) AS ext
  WHERE contact.id = ext.id
  LIMIT 0,20
  ORDER BY nome ASC

Così uso una subquery che mi tira fuori una tabella con tutti i dati esterni "normalizzati" e io posso prendere solo i primi 20 e volendo aumentare la clause WHERE assottigliando il result set.
Ok, dite... e allora? Ecco:

1 - ci sono ancora le UNION che mi puzzano.
2 - Io sto cercando solo i primi 20 contatti. Per creare la tabella virtuale con la subquery io mi smazzo tutta li lista degli indirizzi e dei numeri. Se ho registrato 1000 contatti ed ho 2 indirizzi a persona sono 2000 indirizzi... e me ne servono solo i primi 20. Non posso usare LIMIT, limiterei il result set della subquery, così se il primo contatto ha 20 indirizzi, non mi tira fuori i successivi.

Ordunque... che si fa? Così funziona... ma sicuramente è meglio il primo approccio.
Mi sarò perso in un bicchier d'acqua? Qualcuno potrebbe risolvermi qualche dubbio?
Come fareste voi?

Per ora sto usando il primo metodo ma vorrei chiarirmi le idee
Grazie mille
Ciao
Yuri

[LeaderGL]

hai provato ad effettuare dei test sul tempo che ci impiega a fare la query quando nel DB ci sono abbastanza dati? (abbastanza nel senso di una statistica reale per la tua applicazione...tipo è l'agenda di un ragazzo non avrà mai 100000 indirizzi...mentre quella di un azienda è probabile...)

La UNION credo sia difficile da eliminare dato che cmq devi effettuare due SELECT ed unirne i risultati...al limite puoi provare con

SELECT contact.id, contact.nome, contact.cognome, ext.value, ext.label, ext.model
FROM (

   SELECT value, contact_id, label, model
   FROM contact AS co
   LEFT JOIN (
      SELECT value, contact_id, label, model
      FROM indirizzi
      JOIN categories ON category_id = categories.id
   ) AS ic ON co(id)=ic(contact_id)

   UNION

   SELECT value, contact_id, label, model
   FROM contact AS co
   LEFT JOIN (
      SELECT value, contact_id, label, model
      FROM numeri
      JOIN categories ON category_id = categories.id
   ) AS nc ON co(id)=nc(contact_id)
) AS ext

che è giusto per evitare il:

SELECT ...
FROM contact, ...

che è comunque un prodotto cartesiano completo però non so quanto possa essere efficiente quello che ho scritto dato che ci sono più JOIN.

Prova a riempire il database e vedere un po il tempo che ci mette a fare le varie query...anche se nel bene e nel male comunque ti serve qualche JOIN e la UNION.



------------------- PS -------------------
Ho appena letto delle FOREIGN KEY che mi erano sfuggite prima...potresti usare il NATURAL JOIN risparmiando forse un po di codice...

[forrestG]

Ora non posso fare test seri sulle performance. Per ora guardo con EXPLAIN i risultati di MySQL e i tempi indicativi della query, ma ho troppi pochi dati perchè siano di un certo peso. Quando avrò il sistema un po' più avanzato le farò di sicuro.

Quello che mi premeva concettualmente era di non dover girare tutte le tabelle esterne nella subquery, visto che in realtà non solo sono "indirizzi" e "numeri" ma qualcuna di più. E con la tua query posso in effetti limitare la "scansione" ai soli primi TOT contatti...

Sono alle prime armi con MySQL / SQL e ho sempre paura di complicare le cose più del dovuto e far sedere l'applicativo già in fase di progetto.
Al di là dei casi particolari, come modus operandi è meglio 1 query un po' complessa oppure 100 query semplici e veloci ? O semplicemente è da considerare caso per caso ?

Per le natural JOIN prima vado a vedere cosa sono...
Le mie foreign_key sono più che altro di concetto per indicare che la tal colonna è legata ad un altra tabella. Non ho ancora mai utilizzato le reali potenzialità del mezzo.

W l'innnioranza...

Grazie mille!
Yuri

[LeaderGL]

Originariamente inviato da forrestG
Al di là dei casi particolari, come modus operandi è meglio 1 query un po' complessa oppure 100 query semplici e veloci ? O semplicemente è da considerare caso per caso ?

Eh questa è una bella domanda...dipende sempre dalle situazioni cmq credo sia meglio avere 1 query un po più complessa ma performante che tante query seppur semplici perchè ipotizzando un uso intensivo della pagina potresti avere 10000 persone che la visualizzano e le query non sono più 100 ma 100*10000 il che è male.

Originariamente inviato da forrestG
Per le natural JOIN prima vado a vedere cosa sono...
Le mie foreign_key sono più che altro di concetto per indicare che la tal colonna è legata ad un altra tabella. Non ho ancora mai utilizzato le reali potenzialità del mezzo.

W l'innnioranza...

Grazie mille!
Yuri

Il NATURAL JOIN è un JOIN però mentre il normale JOIN è un prodotto cartesiamo AxB completo il NATURAL JOIN ti restituisce solo le tuple che hanno effettivamente una relazione con l'altra tabella di JOIN (un po come il JOIN ... ON ...) però te l'ho suggerito perchè magari può avere delle ottimizzazioni che lo rendono più veloce (bisogna sempre testare).

Però il NATURAL JOIN effettua il JOIN prendendo in considerazione proprio le FOREIGN KEY per trovare in automatico gli attributi di JOIN.

Esempio:

// DEFINIZIONE DELLE TABELLE PER L'ESEMPIO
CREATE TABLE utenti (
  idutente INT10) NOT NULL auto_increment,
  nome VARCHAR(64) NOT NULL,
  PRIMARY KEY (idutente)
) ENGINE=InnoDB;

CREATE TABLE ordini (
  idordine INT(10) NOT NULL auto_increment,
  idutente INT(10),
  totale DECIMAL(5,2),
  PRIMARY KEY (idordine),
  FOREIGN KEY (idutente) REFERENCES utenti(idutente)
) ENGINE=InnoDB;


// INTERROGAZIONE DB
SELECT idutente, nome, idordine, totale
FROM utenti
NATURAL JOIN ordini

restituisce solo le tuple per quegli utenti che realmente hanno effettuato un ordine e l'attributo di JOIN è quello sulla chiave esterna...per questo dico che magari ha qualche ottimizzazione sul funzionamento rispetto al generico JOIN...però è da vedere...

[forrestG]

Grazie mille!

Sono un po' indietro con il resto però se riesco a fare qualche prova faccio sapere

Sicuramente appena posso provo le NATURAL JOIN perchè è esattamente quello che pensavo quando ho messo giù i DB.

Grazie ancora.
Veramente di aiuto.

Ciao
Yuri