On 4/7/2015 4:12 PM, Rajeev Prasad wrote:
hello Masters,
I am a novice, and I am wanting to know how to achieve this:
1million plus row in a table.
user runs a search, gets some results. I want to store this result in memory in
a way, so that user can fire more SQL searches on this result. How is this
done? I want this to go atleast upto 20 levels down.
in addition, lets say when I am 4th level down, can I have the previous levels
intact for making fresh searches on them?
I also want to store some queries, which produce level X result, in a manner
that it speeds the process in future (user do not have to make multiple
searches to get to the result)
initial Table||---1st search run on initial table (level 1) |
|-----2nd search run on previously obtained result rows (level 2)
any help is highly appreciated.
thank you.
Temporary tables are going to become your very good friends. They will
be how you store your results for later reuse. You can pick from any
available storage engines to that instance. If your "levels" are going
to have a lot of data in them, then you can exhaust your heap if you
store them all using the MEMORY storage engine. For those, you will want
to use InnoDB or MyISAM.
The advantage to using temporary tables is that they can have indexes on
them. You can create the indexes when you create the table or you can
ALTER the table later to add them.
CREATE TEMPORARY TABLE Level1(key(a)) ENGINE=INNODB SELECT
a,b,c,d...FROM source_data;
CREATE TEMPORARY TABLE Level2 ENGINE=MEMORY SELECT ... FROM Level1
ALTER TABLE Level2 ADD KEY(d,c);
If you don't want the column names and data types determined for you by
the results of the SELECT, you can create define the columns explicitly
then populate the table using INSERT...SELECT... instead.
CREATE TEMPORARY TABLE name_goes_here (
a int
, b varchar(50
, c datetime
...
) ENGINE=... (pick which engine you want to use or let it chose the
default for that database by not using any ENGINE= as part of the
definition)
Yours,
--
Shawn Green
MySQL Senior Principal Technical Support Engineer
Oracle USA, Inc. - Hardware and Software, Engineered to Work Together.
Office: Blountville, TN
You or someone you know could be a presenter at Oracle Open World! The
call for proposals is open until April 29.
https://www.oracle.com/openworld/call-for-proposals.html
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql
