Help In Understanding "export Type" In Phpmyadmin

[SlickVic78]

Hello All-

I noticed that my host recently updated their version of phpMyAdmin. Within Export, I see that there is now a new dropdown under the Data section named "Export Type". I assume the default was always set to "Insert", but having a better understanding what each one, "Insert", "Update", and "Replace", does would be very helpful.

Would someone please explain to me what each of these options does?

Also, when backing up my tables, I always used the option under the Data section "Extended Inserts". What is the difference between the options there: "Complete Inserts", "Extended Inserts", and now "Use Delayed Inserts"?

I have attached a snapshot as to what I am looking at here...

Many thanks!
-SlickVic78

[war59312]

Click the doc. link to find out. lol

http://127.0.0.1/mysql/Documentation.html#faqexport



[6.12] I want to understand some Export options.
"Complete inserts" adds the column names on every INSERT command, for better documentation (but resulting file is bigger).
"Extended inserts" provides a shorter dump file by using only once the INSERT verb and the table name.
"Enclose table and field names with backquotes" ensures that field and table names formed with special characters are protected.
"Add AUTO_INCREMENT value" ensures, that AUTO_INCREMENT value (if any) will be included in backup.
"Add into comments" includes column comments, relations and MIME types set in the PMA-DB in the dump as SQL comments (/* xxx */).

That should answer your question.

[SlickVic78]

Hey war59312,

Thanks for this info... This partially answers my second question in my previous post... What about "Use Delayed Inserts"?

Anyone have any info on the first part of my previous post?

Much thanks!
-SlicKVic78

[JacKDynne]

Quote:

The DELAYED option for the INSERT statement is a MySQL-specific option that is very useful if you have clients that can't wait for the INSERT to complete. This is a common problem when you use MySQL for logging and you also periodically run SELECT and UPDATE statements that take a long time to complete. DELAYED was introduced in MySQL Version 3.22.15. It is a MySQL extension to ANSI SQL92.

INSERT DELAYED only works with ISAM and MyISAM tables. Note that as MyISAM tables supports concurrent SELECT and INSERT, if there is no free blocks in the middle of the data file, you very seldom need to use INSERT DELAYED with MyISAM. See section MyISAM Tables.

When you use INSERT DELAYED, the client will get an OK at once and the row will be inserted when the table is not in use by any other thread.

Another major benefit of using INSERT DELAYED is that inserts from many clients are bundled together and written in one block. This is much faster than doing many separate inserts.

Note that currently the queued rows are only stored in memory until they are inserted into the table. This means that if you kill mysqld the hard way (kill -9) or if mysqld dies unexpectedly, any queued rows that weren't written to disk are lost!

The following describes in detail what happens when you use the DELAYED option to INSERT or REPLACE. In this description, the ``thread'' is the thread that received an INSERT DELAYED command and ``handler'' is the thread that handles all INSERT DELAYED statements for a particular table.

When a thread executes a DELAYED statement for a table, a handler thread is created to process all DELAYED statements for the table, if no such handler already exists.
The thread checks whether or not the handler has acquired a DELAYED lock already; if not, it tells the handler thread to do so. The DELAYED lock can be obtained even if other threads have a READ or WRITE lock on the table. However, the handler will wait for all ALTER TABLE locks or FLUSH TABLES to ensure that the table structure is up to date.
The thread executes the INSERT statement, but instead of writing the row to the table, it puts a copy of the final row into a queue that is managed by the handler thread. Any syntax errors are noticed by the thread and reported to the client program.
The client can't report the number of duplicates or the AUTO_INCREMENT value for the resulting row; it can't obtain them from the server, because the INSERT returns before the insert operation has been completed. If you use the C API, the mysql_info() function doesn't return anything meaningful, for the same reason.
The update log is updated by the handler thread when the row is inserted into the table. In case of multiple-row inserts, the update log is updated when the first row is inserted.
After every delayed_insert_limit rows are written, the handler checks whether or not any SELECT statements are still pending. If so, it allows these to execute before continuing.
When the handler has no more rows in its queue, the table is unlocked. If no new INSERT DELAYED commands are received within delayed_insert_timeout seconds, the handler terminates.
If more than delayed_queue_size rows are pending already in a specific handler queue, the thread requesting INSERT DELAYED waits until there is room in the queue. This is done to ensure that the mysqld server doesn't use all memory for the delayed memory queue.
The handler thread will show up in the MySQL process list with delayed_insert in the Command column. It will be killed if you execute a FLUSH TABLES command or kill it with KILL thread_id. However, it will first store all queued rows into the table before exiting. During this time it will not accept any new INSERT commands from another thread. If you execute an INSERT DELAYED command after this, a new handler thread will be created.
Note that the above means that INSERT DELAYED commands have higher priority than normal INSERT commands if there is an INSERT DELAYED handler already running! Other update commands will have to wait until the INSERT DELAYED queue is empty, someone kills the handler thread (with KILL thread_id), or someone executes FLUSH TABLES.
The following status variables provide information about INSERT DELAYED commands: Variable  Meaning 
Delayed_insert_threads  Number of handler threads 
Delayed_writes  Number of rows written with INSERT DELAYED 
Not_flushed_delayed_rows  Number of rows waiting to be written 
You can view these variables by issuing a SHOW STATUS statement or by executing a mysqladmin extended-status command.
Note that INSERT DELAYED is slower than a normal INSERT if the table is not in use. There is also the additional overhead for the server to handle a separate thread for each table on which you use INSERT DELAYED. This means that you should only use INSERT DELAYED when you are really sure you need it!

Hope that helps some

/JD

[SlickVic78]

JacKDynne,

That helps out ALOT! A Delayed Insert can be useful if you have a very busy site where users are accessing the tables frequently... I guess as soon as no tables are being accessed within the DB, that is when the process kicks off to insert/update/replace the information within the specified tables. But the flaw is if MySQL gets killed in the process of the update, you lose that data that was not yet placed within the DB. Makes sense!

Thanks for the help. I pretty much assume I now know the roles of "Insert", "Update", and "Replace".

Insert - places infomation specifies within a new row within a table
Update - checks to see if rows are identicle, if not update the row with the new info
Replace - replace a row with new info

If I am wrong, someone please correct me.

Again, thanks for all your help in helping me understand all this DB jargon!

-SlickVic78

[JacKDynne]

/JD