Re: store search result as new table in memory
> From: Lucio Chiappetti > > On Tue, 7 Apr 2015, shawn l.green wrote: > >> 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. > > if they are big, using proper indices is a must to get quick responses. If your temp tables are read-mostly, create the table with the index. If you're doing a lot of writes, consider adding the index after the insertions. And the proper answer to any question of moderate or greater complexity is, "It depends..." :-) I have never let my schooling interfere with my education. -- Mark Twain Jan Steinman, EcoReality Co-op -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: store search result as new table in memory
On Tue, 7 Apr 2015, shawn l.green wrote: Temporary tables are going to become your very good friends. yes I do use temporary tables a lot 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. if they are big, using proper indices is a must to get quick responses. -- Lucio Chiappetti - INAF/IASF - via Bassini 15 - I-20133 Milano (Italy) For more info : http://www.iasf-milano.inaf.it/~lucio/personal.html Do not like Firefox >=29 ? Get Pale Moon ! http://www.palemoon.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
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
Re: store search result as new table in memory
W dniu 07.04.2015 o 22:12, Rajeev Prasad pisze: 1million plus row in a table. user runs a search, gets some results. MySQL comes with query-cache, once you run your SELECT statement the results are kept in memory. Try it by running big query and then rerun it, the second time it will take miliseconds to complete. 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) I don't really understand the point of it, quering and later adding another WHERE statements would be OK. It all really depends on what are you writing your client in. You can check out Memory engine in MySQL; it provides a way to create a proper MySQL struct (no blobs/big text) that is stored in server's memory. And if searching is really your bread and butter you can use MySQL as a storage engine that feeds into something like Elastic Search. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
store search result as new table in memory
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.
