Hello ALUG,
I hope you don't mind me asking a MySQL question here?
I've got a very slow query on my database which uses a lot of LEFT JOINs in order to do a sort of keyword search across a number of tables. It seems to me that if I could create a sort of permanent temporary table which stores the result of this query, add a FULLTEXT index to it and then do my keyword searches on this table things would be a lot quicker. It would be like a sort of dynamic or index table because its contents would have to change as the other tables change - it would have to reflect the current state of the rest of the database.
Does MySQL provide functionality to do this sort of thing? If not I suppose I could set up a cron job to perform the query and update the table every 10 minutes or so...
I've searched for things like "temporary tables", "index tables" and "dynamic tables" but haven't really found anything particularly useful.
Any thoughts?
Cheers, Richard