On Thu, 2006-03-16 at 09:56 +0000, Stuart Fox wrote:

...

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

You coultd try a "view" if you are using mysql 5, not sure how you do them but it may be what you are after

Stuart

Thanks for this suggestion.

I've just got around to upgrading my MySQL and trying it out. I found the VIEW syntax all right but unfortunately its a bit like a sort of "query template" and it actually /performs/ the defining SELECT statement every time you make a query on the view. So, of course, that doesn't solve the problem because it stills takes around 10 seconds to execute.

However, I have been reading the (woeful) MySQL manual and have found that you can create a table using a SELECT statement to define and populate it /and/ it lets you add a FULLTEXT index at creation time. So I've done this and it works great. Of course, the table takes around 10 seconds to create but I only need to re-build it every few hours and, because its one table with a FULLTEXT index, queries are really quick.

I just thought I'd let you know how I got on...

Cheers, Richard