Databases
StackOverflow2013
Postsdbo
POExport/import 50-million row database without the index?
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POExport/import 50-million row database without the index?
    primarykey
    data
    text
    <p>I have a lot of data - 50 million rows. But because of sub-optimal choices, I've chosen wrong indexes and now inserts take a lot of time (60 seconds to insert 1000 rows).</p> <pre><code>CREATE TABLE `slots` ( `customerid` int(11) NOT NULL, `orderid` int(11) NOT NULL, `queueid` int(11) NOT NULL AUTO_INCREMENT, `item_id` int(3) NOT NULL, `variable1` int(3) NOT NULL, `variable2` int(3) NOT NULL, `variable3` int(3) NOT NULL, `variable4` int(3) NOT NULL, `variable5` int(3) NOT NULL, `variable6` int(3) NOT NULL, `variable7` tinyint(1) NOT NULL, `variable8` tinyint(1) NOT NULL, `variable9` tinyint(1) NOT NULL, PRIMARY KEY (`customerid`,`orderid`,`queueid`), UNIQUE KEY `queueid` (`queueid`), KEY `orderid` (`orderid`) ) ENGINE=InnoDB AUTO_INCREMENT=25883472 DEFAULT CHARSET=latin1 </code></pre> <p>I think it's because the PRIMARY KEY is multi-column and the table needs to be reindexed after each insert (as those <code>customerid</code> and <code>orderid</code> aren't inserted in any asccending/descending fashion but in random places). If I had a primary key on <code>queueid</code> that is autoincrement, it would speed up the inserts considerably?</p> <p>Is it possible for me to export the table without the broken indexes? And then re-index it with the better key?</p> <p>I've tried deleting the keys with a SQL statement but it took a long time and I interrupted it. Is there any way to see the progress? I won't wait if I don't know how long I need to - it could be days after all?</p> <p>I need to re-index this table because it will have 1 billion rows eventually and the insert performance will deteriorate even more.</p> <p>Or maybe it's better to discard what I've already inserted and start a new table with proper indexes? And don't bother with reusing the old data?</p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. UShalfer
    1. USuser3010273
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POExport/import 50-million row database without the index?
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POExport/import 50-million row database without the index?
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POExport/import 50-million row database without the index?
      1. This table or related slice is empty.
      1. VTUpMod
    1. COshow your insert query. and last tried code
      singulars
      1. POExport/import 50-million row database without the index?
      1. USNathan Srivi
    2. CORight now the inserting PHP script works like this: Get the data. Create 500-1000 rows INSERT IGNORE statement ( to insert them in one "go" and not by having 500-1000 separate INSERT operations ). Execute the INSERT IGNORE query.
      singulars
      1. POExport/import 50-million row database without the index?
      1. USuser3010273
    3. COFew more things to try: (1) add your data using multiple-row inserts, (2) batch a number of inserts together (e.g. 1000) inside a transaction, (3) both of the above together. That might net you some performance improvements.
      singulars
      1. POExport/import 50-million row database without the index?
      1. UShalfer
 

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