[MySQL] Foreign Keys + Full Text

[andr3a]

MySQL versione 4, default charset utf8, default engine innoDB,

sto riprogettando un pezzo di database ed ho alcune domande.

Per avere la funzionalita' FULL TEXT l'engine deve essere MyISAM, per avere le foreign keys l'engine deve essere InnoDB, e deve esserlo per entrambe le tabelle (padre, figlia/e)

Vorrei sapere come gestite di solito questo caso, nel senso che io ho rimosso i dati dinamici che mi interessano e li ho allacciati con un ID primary unique che sara' la fotocopia della tabella centrale con le foreign keys, ma usando una MyISAM.

Il tutto per avere quante piu' tabelle fixed possibili, al fine di migliorare le performances, e per avere l'opzione FULL TEXT in quei dati dinamici che mi interessano quali TYNITEXT, TEXT, o MEDIUMTEXT

Un esempio e' questo:

- tabella A con index int auto_increment primary
- tabella B con index propria int auto_increment e foreign key dell'index in A con ON DELETE CASCADE
- tabella C con index propria int che sara' identica a quella B, piu' dati di altro tipo dinamico, TEXT o altro

In realta' le relazioni sono molte di piu' ma questo e' il caso base che vorrei proporvi, a voi sembra corretto?

L'idea e' di usare joins sulle foreign per avere i dati, e il cascade per eliminarli in automatico dalla struttura (rimuovi un id, tutta la struttura annessa si syncronizza eliminando info pe rquell'id salvaguardando spazio) ma mi serve una FULL TEXT per avere piu' potenza con le ricerche.

Il problema e' che cosi' devo joinare l'ultima tabella "a mano" usando l'id della tabella B, e quando elimino qualche id da A devo ricordarmi di eliminare anche in C, ma senza avere riferimenti ad A (e qui e' fattibile tramite una query tipo:
DELETE FROM C WHERE index_c NOT IN(SELECT index_b FROM B);
)

Problema sormontabile, ma ce n'e' un altro ... non riesco a rappresentare tramite DBDesigner o MySQL Workerbench la relazione 1:1 che esiste tra l'id della tabella MyISAM C e quello della tabella B ... poiche' mi pianta foreign ovunque sulla C anche se in realta' non sono possibli/compatibili

Dico male? ... Insomma il sunto e':
- come gestiresete il caso foreign a cascata piu' full text in fondo alla cascata
- come raprresentereste in UML like, l'ultima relazione

Grazie per gli eventuali consigli

[andr3a]

Mysqlworkbench mi ha validato tutto con successo, senza warnings ... quindi sembra che per consistenza, logica, integrita', etc, vada tutto bene ... ma mi resta il dubbio su quella relazione manuale, non ufficialmente rappresentata nello schema ... attendo ancora consigli, grazie

[andr3a]

quale parte non e' chiara? :master:

[piero.mac]

Originariamente inviato da andr3a
quale parte non e' chiara? :master:

questa:

MySQL versione 4, default charset utf8, default engine innoDB,

[andr3a]

Originariamente inviato da piero.mac
questa:

foreign keys vanno solo su InnoDB in MySQL 4 ... le MyISAM le implementeranno ma non perfette, sempre non ci siano già in MySQL 5

Ma magari volevi dirmi che non si capisce niente fin dalla prima riga?

Azzarola piero, ed io che speravo proprio in te

[piero.mac]

tu hai bisogno di peculiarita' specifiche di due motori diversi. E sei con una versione indecifrabile della 4 .... la 4.0 e' ben diversa dalla 4.1 cosi' come la 5.0 lo e' dalla futura 5.1

quindi portabilita' ... da definire. Se vuoi un parere qualsiasi... decidi quale e' la specificita' che piu' ti serve nella versione 4(.0) (penso full text) e fai a manina i controlli di relazione ed integrita' tra le tabelle. Oppure passa alla 4.1-5.0 e prova con i trigger a costruire quello che ti serve sul motore myisam

Se funziona l'accrocchio tra InnoDb e MyIsam nella 4(.0) lascia che funzioni e forse basta che fai il controllo di integrita' a manina sulla tabella MyIsam, ma pensa di dover rifare quasi tutto se e quando passeranno ad una versione superiore di mysql.

[andr3a]

spe, oggi ho provato con 1.666.000 records di relazioni, ed avevo risposte in 0.4 sfruttando MATCH AGAINST su FULLTEXT, in coda a tutte e relazioni in foreign keys ed InnoDB ... quindi probabilmente andrà bene, ma mi domando perchè dici che dovrò cambiare tutto poi ... non mi basterà passare a modello MyISAM?

Comunque MySQL 4.0.qualcosa ... ma domani ti dico la versione esatta che useremo in produzione.

Inoltre pensavo che se si cambierà versione MySQL, si potrà riutilizzare lo schema con relazioni su join manuali ... per il delete in cascade

Ma possibile che sia così drastico il passaggio tra versioni di MySQL? Voglio dire, uno si sbatte per studiarsi ogni singola relazione cercando di evitare ogni singolo numero duplicato, validando, etc, etc ... e questi quando cambiano ti demoliscono il lavoro fatto prima?

[piero.mac]

Originariamente inviato da andr3a
Ma possibile che sia così drastico il passaggio tra versioni di MySQL? Voglio dire, uno si sbatte per studiarsi ogni singola relazione cercando di evitare ogni singolo numero duplicato, validando, etc, etc ... e questi quando cambiano ti demoliscono il lavoro fatto prima?

lo e', anche se non sempre a livello di struttura come tra la 3 - 4.0 e tra la 4.0 - 4.1/5.0

basti dire che viene suggerito di non saltare l'aggiornamento tra versioni di release di punto strutturale diverso. Sinceramente adotterei il motore che piu' mi e' congeniale e poi lascerei le cose come sono aggiornando semplicemente alle versioni successive tramite il backup.

Se poi a nuova versione ci saranno nuove prestazioni richieste ... qualcuno pensera' all'aggiornamento dovutamente retribuito.... mentre se fai qualcosa ora che dovra' servire anche al domani ... se andra' avrai fatto solo il tuo dovere e manco ti diranno grazie, ma se non andra' ti prenderai del pirla.

[andr3a]

piero, io spingo per passare alla 5 da quando sono qua, per via delle views che ci servono, e che ho dovuto inventare a mano pe rla versione 4

Allo stesso tempo il mio diagramma non fa una piega, e non mischia i due motori, semplicemente aggiunge una join all' "ultimo stadio della query", permettendo ricerche in FULLTEXT.

Funziona perfettamente, lo schema e' stravalido, non ridondante, ed estremamente efficiente ( appunto piu' di un milione e mezzo di records in relazione e tempi praticamente reali )

L'efficienza e' data dall'analisi e la completa separazione di dati dinamici da quelli statici, quindi tutte le relazioni in foreign sono strutturate su tabelle fixed, che volano rispetto le altre.

La manutazione di delete in cascata e' una sola query in piu', che ho gia' scritto ... quindi, la mia domanda era: voi come avreste fatto?

Tu mi dici: avrei aggiornato MySQL, ebbene non posso farlo, oppure: avrei usato il motore piu' congeniale, ebbene per me sono entrambi congeniali perche' vogliamo consistenza e fulltext, e MyISAM e' estraneo alle relazioni, seppur indicizzato con ina INDEX UINT(10) 1:1 sull'ultimo stadio delle stesse.

Mysqlworkbench mi da ok su tutti i piani dello schema, io ho testato a mano un db corposo e confermo questi ok.

Le contro indicazioni sono gli aggiornamenti futuri, ma se MyISAM implementera' le foreign come InnoDB, devo solo cambiare engine alle tabelle, mentre se InnoDB implementera' FULLTEXT, devo solo cambiare engine ad una tabella ... insomma, anche come manutenzione futura e aggiornamenti, io non vedo nulla di problematico, tanto piu' che anche avessi queste features usando un solo engine, dubito che MyISAM togliera' FULLTEXT o che InnoDB togliera' le foreign keys, ergo potrei lasciarlo cosi' per 5 anni anche passassimo a MySQL 6 ... dico male?

Riguardo il discorso del pirla: quello che e' considerato il guru dell'ufficio (prima che arrivassi io :sboro': ) nemmeno sapeva che esistono le foreign keys ... quindi questo redesign, seppur ibrido in termini di engines, e' 20 piani sopra l'attuale, dove l'attuale farebbe sorridere qualunque persona che ha studiato minimamente databases e relazioni.

Grazie lostesso per gli interventi, i tuoi sono sempre preziosi

P.S. la relazione irrappresentabile in Mysqlworkbench, l'ho disegnata a mano con Paint.NET sulla PGN esportata