Re: results of the query as a table
Dear Steve, On 27.10.2009, at 16:19, Steve Edberg wrote: Or you could do an ALTER TABLE afterwards to add appropriate indexes. And are you familiar with the EXPLAIN command to help optimize queries/decide what indexes to add? Thanks for this! I have added indicies with ALTER TABLE. And using EXPLAIN and ANALYZE TABLE I found out that in fact I am creating a huge Cartesian product joining fields of two tables which are not indexed! No wonder it took ages to get this query done, I used up 99% of CPU. Definitely it can and it should be optimized. Thanks for your tips again. Cheers, Olga -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: results of the query as a table
At 3:29 PM + 10/27/09, Olga Lyashevska wrote: On 27.10.2009, at 15:11, Steve Edberg wrote: At 2:59 PM + 10/27/09, Olga Lyashevska wrote: Dear all, I run a query and I try to save results as a table. On the results of this first query I want to run yet another query (perhaps a few). I have been trying to use CREATE VIEW statement, which works fine, except for the fact that fields are not indexed because as I understand it indices cannot be created on views. It really affects the performance, making it nearly impossible to run any further queries. I am aware that it is a rather trivial problem, but still I did not manage to find a solution which would meet my requirements. So my question is: are there any other possibilities to save results of the query as a table so that they will be re-used to run yet another query? Thanks in advance, Olga CREATE TABLE ... SELECT should do what you want. For example CREATE TABLE foo SELECT thing1,thing2,concat(thing3,thing4) as thing5 from bar where thing4 like 'baz%' order by thing1 desc You could create a TEMPORARY table if needed (CREATE TEMPORARY TABLE...). Assuming version 5.0: Thanks Steve. It is solved! Shall I add indices manually to speed up query? It would probably help, yes. As it mentions near the bottom of the CREATE TABLE documentation page, you can override column definitions and create indexes in the same statement, something like: CREATE TABLE foo (a TINYINT NOT NULL), c, unique(c) SELECT b+1 AS a, c FROM bar; (never tried that myself). Or you could do an ALTER TABLE afterwards to add appropriate indexes. And are you familiar with the EXPLAIN command to help optimize queries/decide what indexes to add? http://dev.mysql.com/doc/refman/5.0/en/create-table.html http://dev.mysql.com/doc/refman/5.0/en/alter-table.html http://dev.mysql.com/doc/refman/5.0/en/query-speed.html http://dev.mysql.com/doc/refman/5.0/en/explain.html - steve -- ++ | Steve Edberg edb...@edberg-online.com | | Programming/Database/SysAdminhttp://www.edberg-online.com/ | ++ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: results of the query as a table
On 27.10.2009, at 15:11, Steve Edberg wrote: At 2:59 PM + 10/27/09, Olga Lyashevska wrote: Dear all, I run a query and I try to save results as a table. On the results of this first query I want to run yet another query (perhaps a few). I have been trying to use CREATE VIEW statement, which works fine, except for the fact that fields are not indexed because as I understand it indices cannot be created on views. It really affects the performance, making it nearly impossible to run any further queries. I am aware that it is a rather trivial problem, but still I did not manage to find a solution which would meet my requirements. So my question is: are there any other possibilities to save results of the query as a table so that they will be re-used to run yet another query? Thanks in advance, Olga CREATE TABLE ... SELECT should do what you want. For example CREATE TABLE foo SELECT thing1,thing2,concat(thing3,thing4) as thing5 from bar where thing4 like 'baz%' order by thing1 desc You could create a TEMPORARY table if needed (CREATE TEMPORARY TABLE...). Assuming version 5.0: Thanks Steve. It is solved! Shall I add indices manually to speed up query? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: results of the query as a table
At 2:59 PM + 10/27/09, Olga Lyashevska wrote: Dear all, I run a query and I try to save results as a table. On the results of this first query I want to run yet another query (perhaps a few). I have been trying to use CREATE VIEW statement, which works fine, except for the fact that fields are not indexed because as I understand it indices cannot be created on views. It really affects the performance, making it nearly impossible to run any further queries. I am aware that it is a rather trivial problem, but still I did not manage to find a solution which would meet my requirements. So my question is: are there any other possibilities to save results of the query as a table so that they will be re-used to run yet another query? Thanks in advance, Olga CREATE TABLE ... SELECT should do what you want. For example CREATE TABLE foo SELECT thing1,thing2,concat(thing3,thing4) as thing5 from bar where thing4 like 'baz%' order by thing1 desc You could create a TEMPORARY table if needed (CREATE TEMPORARY TABLE...). Assuming version 5.0: http://dev.mysql.com/doc/refman/5.0/en/create-table.html - steve -- ++ | Steve Edberg edb...@edberg-online.com | | Programming/Database/SysAdminhttp://www.edberg-online.com/ | ++ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
results of the query as a table
Dear all, I run a query and I try to save results as a table. On the results of this first query I want to run yet another query (perhaps a few). I have been trying to use CREATE VIEW statement, which works fine, except for the fact that fields are not indexed because as I understand it indices cannot be created on views. It really affects the performance, making it nearly impossible to run any further queries. I am aware that it is a rather trivial problem, but still I did not manage to find a solution which would meet my requirements. So my question is: are there any other possibilities to save results of the query as a table so that they will be re-used to run yet another query? Thanks in advance, Olga -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
