Ho sviluppato un'applicazione web che viene utilizzata principalmente per l'archiviazione di tutti i tipi di materiale testuale (documenti, riferimenti a articoli, libri, riviste, ecc.). Nel mio sistema può esserci un determinato numero di tabelle di archivio, ognuna con un proprio schema. Lo schema può essere modificato da un moderatore tramite l'applicazione (immagina qualcosa di simile a una versione davvero scadente di phpMyAdmin).
Gli utenti possono cercare qualsiasi cosa da tutte le tabelle. Utilizzando gli indici FULLTEXT insieme alla ricerca della sottostringa (campi che non supportano l'indicizzazione FULLTEXT) lo script inserisce i risultati di una ricerca in una singola tabella e ordinando questi risultati per la misura della similarità posso facilmente restituire i risultati impaginati.
Tuttavia, questo approccio ha alcuni problemi:
- la ricerca della sottostringa può contare solo sui risultati esatti
- la regola del 50% si applica a tutte le tabelle separatamente e quindi , mysql potrebbe non restituire corrispondenze importanti o eliminare ingenuamente parole comuni.
- è piuttosto costoso in termini di numeri di query e tempi di esecuzione (non è un problema adesso dato che non ci sono ancora molti dati nelle tabelle).
- i dati normalizzati non vengono nemmeno cercati (ho tabelle diverse per categorie, lingue e attacchi file).
La mia soluzione pianificata crea una singola tabella con colonne simili a
id, table_id, row_id, data
Ogni volta che una nuova riga viene creata / modificata / eliminata in una qualsiasi delle tabelle di dati, anche questa tabella centrale viene aggiornata con la colonna data contenente una concatenazione di tutti i campi di una riga. Potrei quindi creare un singolo indice per Sphinx e usarlo per fare ricerche invece.
Esistono soluzioni o best practice più efficienti su come affrontarlo? Grazie.