Re: results of the query as a table

[Steve Edberg]

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                                  edb...@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=arch...@jab.org