Web lists-archives.com

Re: store search result as new table in memory






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