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
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
On Wed, 2006-03-15 at 15:55 +0000, David Reynolds wrote:
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.
Thanks for the tip.
I've added some lines to my /etc/mysql/my.cnf: [mysqld] join_buffer_size = 2M read_buffer_size = 2M sort_buffer_size = 3M
which I stole from someone's website. Does anyone know what sort of values would be "high" here? Or what would be reasonable to use?
Cheers, Richard
On Wed, 2006-03-15 at 16:11 +0000, Richard Lewis wrote:
On Wed, 2006-03-15 at 15:55 +0000, David Reynolds wrote:
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.
Thanks for the tip.
I've added some lines to my /etc/mysql/my.cnf: [mysqld] join_buffer_size = 2M read_buffer_size = 2M sort_buffer_size = 3M
which I stole from someone's website. Does anyone know what sort of values would be "high" here? Or what would be reasonable to use?
Hang on, I've just read the MySQL docs and it says that join_buffer_size only comes into play when you are using joins with no keys. My joins do use primary keys so, according to the docs, I'm better off increasing my key_buffer size. I tried this, all the way up to 256MB but it didn't make any difference.
I think I'll stick with my first idea of an index table because it'll have some other advantages for my app if I can get it to work.
Cheers, Richard
-
David Reynolds -
Richard Lewis