On 15 Mar 2006, at 2:45 pm, Richard Lewis wrote:
Hello ALUG,
I hope you don't mind me asking a MySQL question here?
I don't, dunno about anyone else ;)
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.
You might want to take a look at join_buffer_size and read_buffer_size. I think they are set quite low by default so you may want to make them a bit higher, if you've got the spare memory. I know this isn't quite what you were asking, but it may help to speed up the joins a bit.
You can create temporary tables as well: "create temporary table blah as select * from blop" and the refer to the table blah, but only in the same connection.
Hope that helps.
Cheers,
David