[MySQL] Ottimizzazione di una ricerca nel DB

[fbcyborg]

Salve a tutti,

ho un problema.
Ho una 30ina di tabelle, e dato un nome (ID) in tutte queste 30 tabelle che sono sottotabelle di un'altra, vorrei sapere a quale sottotabella appartiene.
Al momento ho scritto una funzione (Java) che mi fa la scansione di ogni tabella, e quando trova che quel nome è contenuto nella tabella "i", mi restituisce quell'indice (identificativo di una di quelle 30 tabelle).
Ora, se ci sono poche tuple a tabella la cosa è fattibile, ma se ci sono 1000 tuple a tabella, nel caso peggiore, posso avere un costo O(30000).

Voi cosa mi consigliate per ottimizzare la cosa?

[gugoXX]

Ti consiglio di leggere le viste di sistema che contengono le strutture del db.
In particolare dovrai cercare la vista che contiene le Chiavi Straniere.

prova a cercare tra queste

SELECT * FROM information_schema.SCHEMATA S;

For constraints and foreign keys also;

SELECT * FROM information_schema.TABLE_CONSTRAINTS T;

For everything else check this queries;

SELECT * FROM information_schema.CHARACTER_SETS C;
SELECT * FROM information_schema.COLLATION_CHARACTER_SET_APPLICABILITY C;
SELECT * FROM information_schema.COLLATIONS C;
SELECT * FROM information_schema.COLUMN_PRIVILEGES C;
SELECT * FROM information_schema.`COLUMNS` C;
SELECT * FROM information_schema.KEY_COLUMN_USAGE K;
SELECT * FROM information_schema.PROFILING P;
SELECT * FROM information_schema.ROUTINES R;
SELECT * FROM information_schema.SCHEMA_PRIVILEGES S;  
SELECT * FROM information_schema.STATISTICS S;
SELECT * FROM information_schema.TABLE_PRIVILEGES T;
SELECT * FROM information_schema.`TABLES` T;
SELECT * FROM information_schema.TRIGGERS T;
SELECT * FROM information_schema.USER_PRIVILEGES U;
SELECT * FROM information_schema.VIEWS V;

[fbcyborg]

Innanzitutto ti ringrazio, ma mi devi scusare perché non ho mai usato quel database e i valori in esso contenuti.

Ti faccio un esempio per spiegare la mia situazione, sebbene credo che sia chiara nonostante ciò.

Ho una tabella (per esempio) MAGAZZINO, così fatta:

+--------------+--------------+------+-----+---------+-------+
| Field        | Type         | Null | Key | Default | Extra |
+--------------+--------------+------+-----+---------+-------+
| NOME         | varchar(30)  | NO   | PRI |         |       |
| QUANTITA     | float(10,2)  | YES  |     | NULL    |       |
+--------------+--------------+------+-----+---------+-------+

Inoltre, come dicevo, ho una 30ina di tabelle del tipo (MATERIALEX):

+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| NOME  | varchar(30) | NO   | PRI | NULL    |       |
+-------+-------------+------+-----+---------+-------+

Dove il campo MATERIALEX.NOME references MAGAZZINO.NOME, una foreign key, per l'appunto.

Ora, in questo momento se voglio sapere se il materiale "materiale" è in una delle 30 sottotabelle, me le scandisco tutte e 30.

Tu sicuramente mi hai suggerito qualcosa dal quale dovrei "carpire" i link fra una tabella e un'altra, quindi se non ho capito male mi dovrebbe bastare esaminare la tabella MAGAZZINO + qualche tabella in information_schema.
Il problema è che non ho la minima idea di come fare, e non saprei dove andare a mettere il naso.

L'unica soluzione che mi viene in mente è quella di aggiungere un attributo (in un'altra tabella che ora non c'entra nulla, ma dalla quale è partito tutto) che mi memorizzi il tipo di materiale, per ogni materiale, e recuperare il nome della tabella di appartenenza da lì. Ma prima cercavo altre soluzioni.

[gugoXX]

Database pessimamente progettato.
Comunque ti basta una query di questo tipo

SELECT 'TabellaA' FROM MaterialeA WHERE Nome='quellochecerco'
UNION ALL
SELECT 'TabellaB' FROM MaterialeB WHERE Nome='quellochecerco'
UNION ALL
SELECT 'TabellaC' FROM MaterialeC WHERE Nome='quellochecerco'
....
UNION ALL
SELECT 'TabellaZ' FROM MaterialeZ WHERE Nome='quellochecerco'

Una volta lanciata questa query ti restituira' il nome della tabella/e che contiene questo materiale.
Se c'e' stata almeno l'accortezza di mettere un indice sulla colonna Nome su ciascuna tabella, l'operazione ha costo
O(N Log N).
Se i record sono 30.000, anche su ciascuna tabella, e' un costo ridicolo.

Resta la regola che Java, o qualsiasi altro linguaggio di programmazione che userai per interrogare il DB, deve lanciare le query di ricerca.
Non va bene invece leggere tutto il database e scorrere i dati in memoria con Java.

[fbcyborg]

Originariamente inviato da gugoXX

Database pessimamente progettato.

Questo lo dici tu. Scusa ma probabilmente, non sapendo quali sono le specifiche del progetto e le esigenze del committente non puoi dire questo. A questo punto mi piacerebbe sapere come l'avresti progettato tu un database che richiede una "supertabella" e "n sottotabelle".

Originariamente inviato da gugoXX

Comunque ti basta una query di questo tipo

SELECT 'TabellaA' FROM MaterialeA WHERE Nome='quellochecerco'
UNION ALL
SELECT 'TabellaB' FROM MaterialeB WHERE Nome='quellochecerco'
UNION ALL
SELECT 'TabellaC' FROM MaterialeC WHERE Nome='quellochecerco'
....
UNION ALL
SELECT 'TabellaZ' FROM MaterialeZ WHERE Nome='quellochecerco'

Per questo ti ringrazio, e proverò quanto prima!!!!

Originariamente inviato da gugoXX

Una volta lanciata questa query ti restituira' il nome della tabella/e che contiene questo materiale.
Se c'e' stata almeno l'accortezza di mettere un indice sulla colonna Nome su ciascuna tabella, l'operazione ha costo
O(N Log N).
Se i record sono 30.000, anche su ciascuna tabella, e' un costo ridicolo.

Perfetto!!! Proprio quello che mi serviva.

Originariamente inviato da gugoXX

Resta la regola che Java, o qualsiasi altro linguaggio di programmazione che userai per interrogare il DB, deve lanciare le query di ricerca.
Non va bene invece leggere tutto il database e scorrere i dati in memoria con Java.

Userò, Java. Certo, non mi è mai passato per la mente di leggere tutto il database e scorrere i dati in memoria.

Grazie!!!!

[cdimauro]

Originariamente inviato da fbcyborg

Questo lo dici tu.

No, lo dice il buon senso.

Scusa ma probabilmente, non sapendo quali sono le specifiche del progetto e le esigenze del committente non puoi dire questo.

Riportale.

A questo punto mi piacerebbe sapere come l'avresti progettato tu un database che richiede una "supertabella" e "n sottotabelle".

Per lo meno avrei normalizzato il nome del materiale, in modo da ridurre la lunghezza della primary & foreign key e ottimizzare i relativi indici.

Poi questa suddivisione in n sottotabelle mi puzza non poco. Se sono tutte uguali io avrei usato una tabella unica.

C'è ancora oggi gente che crede che dividere dati dello stesso tipo in più tabelle aiuti le prestazioni. Evidentemente non hanno idea di come si struttura un database né tanto meno come funziona "a regime".

[fbcyborg]

Boh, allora si vede che i professori dei corsi di Basi di dati e Sistemi di Gestione di Basi di Dati, nonché di Progetto di Basi di dati che ho seguito in questi anni di Ingegneria Informatica non ci capiscono niente.
Andrò presto a lamentarmi!

Comunque ho provato questo sistema e non funziona:

SELECT 'TabellaA' FROM MaterialeA WHERE Nome='quellochecerco'
UNION ALL
SELECT 'TabellaB' FROM MaterialeB WHERE Nome='quellochecerco'
UNION ALL
SELECT 'TabellaC' FROM MaterialeC WHERE Nome='quellochecerco'
....
UNION ALL
SELECT 'TabellaZ' FROM MaterialeZ WHERE Nome='quellochecerco'

Nel mio caso 'TabellaA' e MaterialeA sono coincidenti.
Non funziona almeno provando la query da sola, senza UNION ALL.

AH, e per la precisione: quelle "30 tabelle" NON sono tutte uguali, alcune hanno campi in più. Per questo sono state fatte.

[cdimauro]

Non c'è bisogno di fare la vittima. La normalizzazione, comunque, dovresti averla studiata.

Nel tuo caso la tabella MAGAZZINO la farei così:

ID INTEGER NOT NULL AUTO_INCREMENT
Nome VARCHAR(30) NOT NULL
Quantita DECIMAL(10,2)

e a seguire tutti i campi in comune a tutte le tabelle MATERIALEX.

In questo modo eseguire query che coinvolgono i campi comuni è più semplice e immediato.

Le tabelle MATERIALEX avranno soltanto:

IDMagazzino INTEGER NOT NULL

e a seguire tutti i campi peculiari del materiale X.

Per gli indici, oltre a Nome li metterai sui campi su cui vengono fatte ricerche oppure ordinamenti più spesso, e soprattutto che discriminano maggiormente i dati.

[gugoXX]

PRova a scrivere il primo pezzo cosi':

SELECT 'TabellaA' as nomeTabella FROM MaterialeA WHERE Nome='quellochecerco'

Ovviamente tutte le tabelle "Materiale" devono avere la colonna "Nome", altrimenti dovrai usarare il nome di colonna corretto...

Comunque quoto il collega.
Una specifica che detti "E ci devono essere N tabelle diverse", non e' una specifica.

[fbcyborg]

Originariamente inviato da gugoXX

SELECT 'TabellaA' as nomeTabella FROM MaterialeA WHERE Nome='quellochecerco'

Grazie, funziona perfettamente ora.

PS: sì, la normalizzazione l'ho studiata ed ammetto di non averla applicata, ma questo è ben diverso da dire che è progettato male, visto che la normalizzazione è una cosa che si fa alla fine (quindi non è che in base a questo si può giudicare un'intera progettazione che è composta di più fasi). Giusto per parlare, non ho normalizzato semplicemente perché il database di questa applicazione non richiede alti livelli, e anche in base al budget di spesa che si è prefissato il cliente, posso permettermi di non averlo ottimizzato come si deve. Sul fatto della progettazione, fidati che ho seguito perfettamente le metodologie che ho imparato nei suddetti corsi.

[cdimauro]

Io non ho giudicato (e non mi posso permettere di farlo) l'intero progetto, ma quel poco che ho visto.

Anche partendo da zero, quella che ho esposto sopra è la strada che avrei seguito comunque. Sarà che ormai sono anni che ho a che fare con database, e mi viene naturale pensare subito a soluzioni di quel tipo.

Per il resto, nessuno ce l'aveva con te.

[fbcyborg]

Ok ok...

Dunque, stavo provando con quel tipo "nuovo" di query.
Ho visto che usando quella, facendo una misurazione in millisecondi, ci mette anche di più di come facevo io, ovvero fare esplicitamente la scansione di tutte le tabelle una dopo l'altra.
All fine mi sembra di capire che quella query faccia la stessa cosa.
Probabilmente manca quella clausola UNION.

[cdimauro]

E' molto strano, perché con le select & union che ha suggerito gugo prima, dovrebbe essere il server a fare tutto, e nella maniera più efficiente.

[fbcyborg]

Ok, proverò a impostare una 30ina di query, vediamo che succede!

[fbcyborg]

Ho provato con le 30 query e UNION ALL.
Avverto un leggero miglioramento di prestazioni effettivamente!

GRAZIE!!!

[cdimauro]

Adesso potresti provare a normalizzare le due tabelle, come avevo indicato prima: vedrai che la situazione migliorerà di molto (con una sola query senza union dovresti poter ottenere quello che ti serve).

[Sint]

Mi intrometto nella vostra discussione perchè interessa anche a me. In particolar modo vorrei approfondire il suggerimento di cdimauro.
Se ho ben capito tu suggerisci (all'atto pratico) di sostituire il campo "nome" con il campo "idmagazzino" nelle sottotabelle, e aggiungere, quindi, il campo id nella tabella magazzino. Se cosi fosse vorrei capire bene perchè modificare un tipo di campo in MATERIALEX ed aggiungerne un altro in MAGAZZINO, dovrebbe migliorare la situazione rispetto ad ora. Quello che suggerisci te non è un semplice "cambio di variabile" (concedimi il termine )??
Grazie.

[cdimauro]

Non c'è soltanto un "cambio di variabile", ma di tipo. Questo perché nella tabella MAGAZZINO l'informazione Nome è di tipo testuale, mentre IDMagazzino è un intero.

Oltre a questo, normalizzando si compatta meglio lo spazio delle tabelle MATERIALEX, perché un intero (a 32 bit) occupa mediamente molto meno spazio rispetto a un campo testuale di lunghezza variabile (diciamo che fino a 2 caratteri il VARCHAR ha un vantaggio). Questo vale anche per l'indice sul campo.

In generale ciò comporta anche delle ricerche più veloci.

[cionci]

Originariamente inviato da fbcyborg

Inoltre, come dicevo, ho una 30ina di tabelle del tipo (MATERIALEX):

+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| NOME  | varchar(30) | NO   | PRI | NULL    |       |
+-------+-------------+------+-----+---------+-------+

Dove il campo MATERIALEX.NOME references MAGAZZINO.NOME, una foreign key, per l'appunto.

Mi puoi giustificare la scelta delle 30 tabelle con la struttura identica ?

[cdimauro]

La struttura non è identica: ci sono tabelle che hanno campi diversi.