Mysql – less time costly

MySQLperformance

I'm migrating a database from DB2 to MySQL. There are big tables, so it takes a huge amount of time. The question here is:

If there's a table with about 9 millions of records with InnoDB storage engine.

What of the next approaches would take less time?

Create the table without indexes, foreign keys or any constraints. Load the data. Alter table to set the constraints
Create the table with constraints and load the data.

In theory the load of the data on an indexed table is slower than on a non indexed table. But creating an index and foreign keys on big tables takes a lot of time.

Best Answer

Generally it is better to load an InnoDB table with nothing more than the PRIMARY KEY. Then do one ALTER TABLE to add all the indexes and foreign keys.

If you don't have an explicit PRIMARY KEY, that is bad. Change a UNIQUE key to PRIMARY, if practical.

But, the foreign keys can be a problem because of dependencies.

Plan A: ALTER one table at a time, but in an order such that there cannot be any FK issues.

Plan B: Disable FKs while doing the ALTERs, then enable them.

In InnoDB, the PK is 'clustered' with the data. Hence, the data is sorted by the PK and stored on disk in that order. So, it is best to have the PK defined, and to supply the data to the loader in PK order. The PK+Data is in a BTree.

Secondary keys (and FKs) involve separate BTrees. Building them incrementally can lead to thrashing in the "buffer pool" (see innodb_buffer_pool_size). Building such an index separately can involve a unix sort -- more efficient for really big indexes.

I would call 9M rows rather big (90th percentile), but not huge.

Another caution: UNIQUE keys need to check as they are inserted. I think that implies that you define UNIQUE keys up front, rather than afterwards.

Related Solutions

Mysql – the optimal solution for converting a database schema

There comes a point when a step-by-step clean-up becomes more work than a clean slate and migrate approach. System availability and time to migrate may factor in to the decision when dealing with larger volumes but at this size, not an issue.

Key factors for me here are:

Renaming foreign key constraints to fit a new application framework.
Refactoring a significant proportion of existing tables.
Low volume of data.

In this situation I'd be very tempted to design a new schema that fits the model you now require and create the necessary scripts to migrate data across (your option 4).

Postgresql – Postgres Performance Over Group by with MAX and MIN

The following indexes may help you:

CREATE INDEX stkx_custloc_ind1
  ON customer_location
  USING btree
  (location_id, customer_id);

CREATE INDEX stkx_custrecpt_ind1
  ON customer_receipt
  USING btree
  (customer_id, receipt_id);

CREATE INDEX stkx_recptprod_ind1
  ON receipt_product
  USING btree
  (receipt_id, status COLLATE pg_catalog."default", is_active, txn_dt_tm);

CREATE INDEX stkx_recptprod_ind2
  ON receipt_product
  USING btree
  (receipt_id, txn_dt_tm, status COLLATE pg_catalog."default", is_active);

Please note, in addition, that your query may be better tuned with an inline view:

SELECT 
        cr.customer_id, 
        min(min_txn_dt_tm), 
        max(max_txn_dt_tm) 
FROM customer_location cl 
JOIN customer_receipt cr ON cl.customer_id = cr.customer_id AND cl.location_id = 'UUID HERE'
JOIN (
        select re_pr.receipt_id, min(txn_dt_tm) as min_txn_dt_tm, max(txn_dt_tm) as max_txn_dt_tm from receipt_product re_pr where status = 'PURCHASED' 
    AND is_active = true group by re_pr.receipt_id) rp ON rp.receipt_id = cr.receipt_id
GROUP BY cr.customer_id
;

Your schema suffers from having the 'id' antipattern. With this you have made a programmer's life easier until the application allows you to enter unexpected data, such as multiple customers having the same receipts.

I hope this helps!

Best Answer

Related Solutions

Mysql – the optimal solution for converting a database schema

Postgresql – Postgres Performance Over Group by with MAX and MIN

Related Question