Re: store search result as new table in memory
From: Lucio Chiappetti lu...@lambrate.inaf.it 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
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.
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
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: Trying to create a new table in memory.
This worked. Thanks very much for your help! Charles On 12/1/06, mos [EMAIL PROTECTED] wrote: At 05:37 PM 11/30/2006, Charles Danko wrote: Hi, I am trying to write a new table into main memory, but I am getting an out of memory error. Each entry consists of 2 medium_int and 1 tiny_int variables, and the table contains just over 100,000,000 rows. By my count, this makes just over 700MB of data. The machine I am using has 2GB, but I am still getting an out of memory error. What am I doing wrong? Thanks! You will likely need to increase the max_heap_table_size if you are using MySQL 5.x. This link may be relevant: http://dev.mysql.com/doc/refman/5.0/en/full-table.html Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Trying to create a new table in memory.
Hi Charles, Charles Danko wrote: Each entry consists of 2 medium_int and 1 tiny_int variables, and the table contains just over 100,000,000 rows. By my count, this makes just over 700MB of data. The machine I am using has 2GB, but I am still getting an out of memory error. What am I doing wrong? You use the memory storage engine If I am getting it right? Does MySQL report that it's out of memory (table full error) or does the OS do so? Are there any indexes (might double the data for your small table)? regards Nils -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Trying to create a new table in memory.
Hi, Thanks for the responses! I am creating the table from an existing (MyISAM) table using the command: CREATE TABLE memRAW (mID SMALLINT NOT NULL, pID MEDIUMINT NOT NULL, mp TINYINT NOT NULL) ENGINE = MEMORY SELECT * FROM RAW; The error that I get is in the MySQL client application: ERROR 1114 (HY000): The table 'memRAW' is full The existing table (RAW) does contain a (large) index (MYI file?), but I was under the impression that this file does not transfer when creating the new table. In any case, using free shows 1.57GB free, which should be enough to fit the table twice, and is just enough to fit both MYD and MYI files for the MyISAM version of the database. I get similar errors when selecting only a subset of the 100,000,000 rows. (i.e. adding a WHERE mID = clause to the end of the select statement). I am running SuSe 10.1 and MySQL version is 5.0.27 linux i686, glibc compiled executibles. I'm hoping that this is just caused by a configuration setting that I am missing when reading the docs. Any ideas? Thanks again! On 12/1/06, Nils Meyer [EMAIL PROTECTED] wrote: Hi Charles, Charles Danko wrote: Each entry consists of 2 medium_int and 1 tiny_int variables, and the table contains just over 100,000,000 rows. By my count, this makes just over 700MB of data. The machine I am using has 2GB, but I am still getting an out of memory error. What am I doing wrong? You use the memory storage engine If I am getting it right? Does MySQL report that it's out of memory (table full error) or does the OS do so? Are there any indexes (might double the data for your small table)? regards Nils -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Trying to create a new table in memory.
At 05:37 PM 11/30/2006, Charles Danko wrote: Hi, I am trying to write a new table into main memory, but I am getting an out of memory error. Each entry consists of 2 medium_int and 1 tiny_int variables, and the table contains just over 100,000,000 rows. By my count, this makes just over 700MB of data. The machine I am using has 2GB, but I am still getting an out of memory error. What am I doing wrong? Thanks! You will likely need to increase the max_heap_table_size if you are using MySQL 5.x. This link may be relevant: http://dev.mysql.com/doc/refman/5.0/en/full-table.html Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Trying to create a new table in memory.
Hi, I am trying to write a new table into main memory, but I am getting an out of memory error. Each entry consists of 2 medium_int and 1 tiny_int variables, and the table contains just over 100,000,000 rows. By my count, this makes just over 700MB of data. The machine I am using has 2GB, but I am still getting an out of memory error. What am I doing wrong? Thanks!
Re: Trying to create a new table in memory.
Charles Danko wrote: Hi, I am trying to write a new table into main memory, but I am getting an out of memory error. Each entry consists of 2 medium_int and 1 tiny_int variables, and the table contains just over 100,000,000 rows. By my count, this makes just over 700MB of data. The machine I am using has 2GB, but I am still getting an out of memory error. What am I doing wrong? Need some more information from you. a) Which storage engine are you using? b) What settings for that storage engine exist in your config file? For instance, if InnoDB, then what is the value of innodb_buffer_pool_size? c) What operating system are you using? Cheers, Jay -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Table in Memory
I have a large table which I like to store into memory . Table looks like Spid_1__0 (recordname varchar(20) primary key, data blob not null ) what is the best way todo this in mysql
Re: Table in Memory
Maybe look at using a HEAP table? Load it on startup from a datasource.. On Wed, 5 Nov 2003, Arnoldus Th.J. Koeleman wrote: I have a large table which I like to store into memory . Table looks like Spid_1__0 (recordname varchar(20) primary key, data blob not null ) what is the best way todo this in mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Table in Memory
Hi, HEAP tables don't currently support TEXT/BLOB columns. My answer about storing the table in memory: don't bother. If you have enough free RAM to use to put the table in memory, the OS will already do it for you after it's accessed. Thus, reading the table (after the first access) should be as fast as putting it on a RAM disk or whatever people might suggest. Matt - Original Message - From: [EMAIL PROTECTED] To: Arnoldus Th.J. Koeleman Cc: [EMAIL PROTECTED] Sent: Wednesday, November 05, 2003 11:03 AM Subject: Re: Table in Memory Maybe look at using a HEAP table? Load it on startup from a datasource.. On Wed, 5 Nov 2003, Arnoldus Th.J. Koeleman wrote: I have a large table which I like to store into memory . Table looks like Spid_1__0 (recordname varchar(20) primary key, data blob not null ) what is the best way todo this in mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
