My design contains a table where almost every entry has a parent it has to point to, within the same table (differently put, self references).

    /* scenario executions */
    CREATE TABLE `boteval`.`scenario_executions` (
      `scenario_id` BIGINT NOT NULL COMMENT 'connects to a scenario id',
      `parent_id` BIGINT NULL COMMENT 'execution id of parent executor (null would mean no parent, i.e. a top level execution)',
      `started` DATETIME(6) NOT NULL,
      `ended` DATETIME(6) NULL COMMENT 'can be null while the execution has not yet ended',
      PRIMARY KEY (`id`),
      UNIQUE INDEX `id_UNIQUE` (`id` DESC));

The use case being one where tree structures need to be incrementally recorded into the table, first the parent, than children, children of children, etc, as the program unfolds.

Using an auto-incrementing key for this table, I have reason to believe it would be extruciatingly slow if every insert needed to happen only after its predecessor had already returned its database-assigned auto incremented key.

On the other hand, I think that batching this workload would be way beyond naive, as a sql that strings together the returned ids would become extremely nested for each buffered batch, not to mention the dependencies between batches.

I have several solutions to this, but I wonder if there are very specific design patterns you would recommend. Maybe one of my design constraints would need to be relaxed: maybe the auto-incrementing, if I can safely supply a unique key from my code. In a way, this might not even really be a good fit for a relational database like MySQL.

Thanks in advance for any comments on good patterns for this kind of scenario!

  • Can you give an example of the specific queries that happen for a particular instance of the workload? Apr 22, 2017 at 20:03
  • I mean for example: "many tree structures need to be incrementally (and concurrently from different threads) recorded into the table". What would that look like? I understand a parent-child relationship but you need to explain the "tree structures" further. Apr 22, 2017 at 20:08
  • Can you show an example of your table(s) where this is happening? Apr 22, 2017 at 20:13
  • Done. I think we can remove our comments now.
    – matanox
    Apr 22, 2017 at 20:15

1 Answer 1

  • In indexes, DESC is ignored. (This will be rectified in version 8.0.)
  • MySQL is happy to use an ASC index (that is, any index) but scan it in descending order.
  • So DROP that redundant UNIQUE index.

That said, I don't see that anything you have described so far could be cause "excruciatingly slow" inserts.

To further elaborate. You have 2 BTrees:

  • One with all the columns, and ordered (ASC) by ID
  • One with just ID and ordered (ASC) by it.

When inserting a new row (without explicitly specifying ID), the server is optimized to insert it into the "last" block of the BTree and bumping the A_I value.

Meanwhile, the UNIQUE index is acting much the same. But it is doubling the amount of work to do, with no benefit.

On the other hand, if you have multiple connections doing the inserts, this may be worth noting -- How the inset works:

  1. Get a new A_I value (well optimized)
  2. Check all UNIQUE indexes for duplicates. This may be slow, since another connection may be locking the adjacent ID.
  3. Insert the row
  4. Throw info into the "Change buffer" for later updating of non-unique indexes. (This does not apply.)

Also worth noting in InnoDB:

  • Each transaction has some overhead -- writing to the undo log, writing the "double write" buffer, etc.
  • Hence, batched insert run much faster because they spread out this overhead over multiple rows. Can you insert, say, 100 rows in a single INSERT? That may literally run 10 times as fast.

Do you really need BIGINT? It takes 8 bytes. INT UNSIGNED takes only 4 bytes, and allows 4 billion values.

  • Thanks for all the helpful information. Given the nature of incremental tree building in my application, no, I cannot conceivably bunch inserts into groups. That's why I am uncertain also after your answer, how terrible it would be to insert one by one. Maybe that's hiding in between the details.. other than I'll be 10 times slower than with e.g. 100 row batches.
    – matanox
    Apr 23, 2017 at 8:46
  • 1
    100 single-rows per second is easy to attain. 1000/sec is not. How fast will you be inserting? You used the word "performance", so I threw the relevant tips your direction. Sure, some applications cannot do batching. Still, there are more complex solutions that can get past 100/sec. If you need such, start a new question aimed at that. (The answer will be longer and messier.)
    – Rick James
    Apr 23, 2017 at 15:49
  • I wonder what should a question ask for that? should it ask how to optimize for many one row inserts?
    – matanox
    Apr 24, 2017 at 0:17
  • 1
    Explain the application, the number of threads providing data, whether batching is practical, the schema, whether normalization is involved. Meanwhile, compute how many GB will be filled up per day! And what will the SELECTs want to do; table scans are killers at that size.
    – Rick James
    Apr 24, 2017 at 0:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.