Hello.
>still stop at the 10M limit on memory tables anyway. What's the value of max_heap_table_size? See: http://dev.mysql.com/doc/mysql/en/server-system-variables.html [EMAIL PROTECTED] wrote: > Thanks that gives me options, yes table was already created what I wanted > was for the table itself to know that when MySql reloads to go an get all > from another table. > > I was understanding this was just something I did when I created the table > the first time as a Character of the table to know on load select * from > Test2 > > This allows me to maintain stability and speed at the same time. While I > write to 2 tables I always read from 1 and reading is done 95 times more > often at least. > > I have set the My.cnf to 128M for memory tables as default but it appears I > still stop at the 10M limit on memory tables anyway. Should I add something > into the creation of the table to override the defaults locally with that > table? > > Thanks > Donny Lairson > President > 29 GunMuse Lane > P.O. box 166 > Lakewood NM 88254 > http://www.gunmuse.com > 469 228 2183 > > > -----Original Message----- > From: Michael Stassen [mailto:[EMAIL PROTECTED] > Sent: Monday, March 14, 2005 8:28 AM > To: [EMAIL PROTECTED] > Cc: Gleb Paharenko; mysql@lists.mysql.com > Subject: Re: Auto loading a table > > > [EMAIL PROTECTED] wrote: > > > [Donny Lairson] Quick bump I never got an answer > > > > I have a table fsearch_temp I use it as a memory table to keep things > > light and fast but after a restart I want to repopulate some data > > automatically. So I thought I just said load from TEST2 which would by a > > myisam table containing the hardbackup I need. But obviously not the > > right way of saying this. I must be reading the instructions wrong can > > someone clarify this for me? > <snip> > > Which instructions are you reading? I expect you get a syntax error, right? > From the manual <http://dev.mysql.com/doc/mysql/en/create-table.html>, the > correct syntax is > > CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name > [(create_definition,...)] > [table_options] [select_statement] > > Gleb Paharenko wrote: >> Hello. >> >>>) ENGINE = MEMORY LOAD FROM TEST2 DEFAULT CHARSET = utf8 AUTO_INCREMENT =0 >> >> You should use select statement, not LOAD. For example: >> CREATE TABLE ..... SELECT * FROM TEST2; >> >> And table options like DEFAULT CHARSET you should put before select > statement. >> See: >> http://dev.mysql.com/doc/mysql/en/create-table.html > > > I think this is accurate but misleading. CREATE ... SELECT adds columns > from the SELECT to the columns defined in the CREATE, so you cannot fix this > simply by getting the last line right. You have to leave out the column > definitions. On the other hand (from the manual page you cite), > > CREATE TABLE ... SELECT does not automatically create any indexes for > you. This is done intentionally to make the statement as flexible as > possible. If you want to have indexes in the created table, you should > specify these before the SELECT statement... > > so you do need to keep the index definitions. Thus, assuming fsearch_temp's > create_definition matches that of table TEST2, to create fsearch_temp as a > copy of TEST2, you would > > CREATE TABLE fsearch_temp > ( PRIMARY KEY (fsearchId), KEY fsearchIp (fsearchIp) > ) ENGINE = MEMORY DEFAULT CHARACTER SET utf8 > SELECT * FROM TEST2; > > but I don't think this is what you want, either. > > First, there is this caveat (from the manual): > > Some conversion of column types might occur. For example, the > AUTO_INCREMENT attribute is not preserved, and VARCHAR columns can > become > CHAR columns. > > To avoid that, you need to first CREATE the table, then populate it with a > copy of TEST2 in a separate INSERT ... SELECT statement. See the manual for > details <http://dev.mysql.com/doc/mysql/en/insert-select.html>. > > In any case, MEMORY tables don't go away unless they are dropped. Only the > rows disappear when mysql stops. If you've previously created this table > and haven't dropped it, it should still exist as an empty table on startup. > In that case, you only need to reload the rows. > > INSERT INTO fsearch_temp SELECT * FROM TEST2; > > Michael > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > > > > -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ ____ __ / |/ /_ __/ __/ __ \/ / Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET <___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
