Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>Did you try the <strong><em>Bulk Data Loading Tips</em></strong> from the <a href="https://dev.mysql.com/doc/refman/5.7/en/optimizing-innodb-bulk-data-loading.html" rel="noreferrer">InnoDB Performance Tuning Tips</a> (especially the first one):</p> <blockquote> <ul> <li><p>When importing data into <code>InnoDB</code>, make sure that MySQL does not have autocommit mode enabled because that requires a log flush to disk for every insert. To disable autocommit during your import operation, surround it with <code>SET autocommit</code> and <code>COMMIT</code> statements:</p> <pre><code>SET autocommit=0; ... SQL import statements ... COMMIT; </code></pre> <p>If you use the mysqldump option <code>--opt</code>, you get dump files that are fast to import into an <code>InnoDB</code> table, even without wrapping them with the <code>SET autocommit</code> and <code>COMMIT</code> statements.</p></li> <li><p>If you have <code>UNIQUE</code> constraints on secondary keys, you can speed up table imports by temporarily turning off the uniqueness checks during the import session:</p> <pre><code>SET unique_checks=0; ... SQL import statements ... SET unique_checks=1; </code></pre> <p>For big tables, this saves a lot of disk I/O because <code>InnoDB</code> can use its insert buffer to write secondary index records in a batch. Be certain that the data contains no duplicate keys.</p></li> <li><p>If you have <code>FOREIGN KEY</code> constraints in your tables, you can speed up table imports by turning the foreign key checks off for the duration of the import session:</p> <pre><code>SET foreign_key_checks=0; ... SQL import statements ... SET foreign_key_checks=1; </code></pre> <p>For big tables, this can save a lot of disk I/O.</p></li> </ul> </blockquote> <p>IMO, the whole chapter is worth the read.</p>
    singulars
    1. This table or related slice is empty.
    1. POInnoDB takes over an hour to import 600MB file, MyISAM in a few minutes
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USnoobish
    1. USPascal Thivent
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POInnoDB takes over an hour to import 600MB file, MyISAM in a few minutes
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COMassive +1. Perfect answer.
      singulars
      1. PO
      1. UST.J. Crowder
    2. COThanks for that pointer! I've gone through the chapter, you quoted the most relevant parts already anyway, and applied and them ran the test again. Faster, but still way slower. Can't believe that. Seems I'm doing some real beginner mistake here.
      singulars
      1. PO
      1. USmark
    3. CO@mfn You're welcome. But indeed, without autocommit, without uniqueness checks, without foreign key checks, you should get similar speed. Do you have any index on that table? Maybe drop them and recreate them after the bulk import.
      singulars
      1. PO
      1. USPascal Thivent
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload