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=gcdmg-mysql-2@m.gmane.org

Re: results of the query as a table

At 2:59 PM +0000 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 edberg@edberg-online.com |
| Programming/Database/SysAdmin http://www.edberg-online.com/ |
+----------------------------------------------------------- -------------+

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: results of the query as a table

On 27.10.2009, at 15:11, Steve Edberg wrote:

> At 2:59 PM +0000 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=gcdmg-mysql-2@m.gmane.org

Re: results of the query as a table

At 3:29 PM +0000 10/27/09, Olga Lyashevska wrote:
>On 27.10.2009, at 15:11, Steve Edberg wrote:
>
>>At 2:59 PM +0000 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 edberg@edberg-online.com |
| Programming/Database/SysAdmin http://www.edberg-online.com/ |
+----------------------------------------------------------- -------------+

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

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=gcdmg-mysql-2@m.gmane.org