0

I recently learned that it is advisable to have deterministic queries when using a replication instance. Therefore, we started adding ORDER BY to our INSERT INTO (...) SELECT (...) FROM X statements. For most of the tables, this was not a problem, but for one table, the runtime increased dramatically when adding ORDER BY.

Without ORDER BY, it takes 4,5 minutes which is acceptable for our purpose. With ORDER BY the runtime is ~25 minutes. Is there anything I can do to speed this up?

By the way, the target table consolidated has an auto increment id, so the order of inserts does matter for the replication. In my understanding, the replication can only use statement-based replication in this situation if we add an ORDER BY.

This is the problematic query:

INSERT into consolidated (sourceTableName, dateOfNotification, 
typeOrBasis, authority, country, countOfNotifications, productDescription, productType, 
productCategory, ProductCategoryCanonical, origin, supplierOrBrand, systemDate) 
SELECT "example_table", ref_sub_year, lower(`type`), "SampleAuthority", "SampleCountry", 
`count`, lower(product_desc),
CASE
  WHEN product_category = 'Human Food' THEN 'food'
  WHEN product_category = 'Housewear and Food Related' THEN 'food contact material'
  WHEN product_category = 'Animal Feed' THEN 'feed' else product_category 
END AS productType, lower(IND_DESC), 'other food items / fixed', manufacturer_country_name, 
lower(manufacturer_legal_name), updated_at FROM example_table
ORDER BY updated_at\G;

EXPLAINof the query above:

*************************** 1. row ***************************
           id: 1
  select_type: INSERT
        table: consolidated
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: NULL
     filtered: NULL
        Extra: NULL
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: example_table
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 7385025
     filtered: 100.00
        Extra: Using filesort

EXPLAINof the query above, without ORDER BY:

*************************** 1. row ***************************
           id: 1
  select_type: INSERT
        table: consolidated
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: NULL
     filtered: NULL
        Extra: NULL
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: example_table
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 7385025
     filtered: 100.00
        Extra: NULL
2 rows in set (0,182 sec)

This is the table definition of the source table example_table:

CREATE TABLE `example_table` (
  `ref_sub_year` date NOT NULL,
  `product_desc` varchar(150) NOT NULL,
  `product_category` varchar(50) NOT NULL,
  `ind_desc` varchar(100) NOT NULL,
  `class_desc` varchar(100) NOT NULL,
  `manufacturer_legal_name` varchar(150) NOT NULL,
  `manufacturer_fei_number` bigint(20) NOT NULL,
  `manufacturer_country_code` varchar(2) NOT NULL,
  `manufacturer_country_name` varchar(50) NOT NULL,
  `manufacturer_country_lat` decimal(6,3) DEFAULT NULL,
  `manufacturer_country_long` decimal(6,3) DEFAULT NULL,
  `count` int(11) NOT NULL,
  `type` varchar(20) NOT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`ref_sub_year`,`product_desc`,`product_category`,`ind_desc`,`class_desc`,
  `manufacturer_fei_number`,`type`),
  KEY `idx_prc_load_consolidated` (`updated_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

EDIT Adding execution plans of only the SELECT part as requested by Ergest Basha:

With ORDER BY:

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: example_table
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 7385025
     filtered: 100.00
        Extra: Using filesort

Without ORDER BY:

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: example_table
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 7385025
     filtered: 100.00
        Extra: NULL
6
  • @Ergest The replication is configured to use "mixed" as replication mode, as this is the default on AWS. So, if I am not mistaken, the replication will be row-based if I just remove the ORDER BY as this will make the statement non-deterministic. This would be ok for us. I posted this question just to get a better understanding why ORDER BY is so costly.
    – binford
    Commented Oct 22, 2022 at 11:04
  • Your INSERT is not positional-dependent. So ORDER BY makes no sense in SELECT. this will make the statement non-deterministic. Only rows ordering is non-deterministic, but, taking into account that INSERT is not positional-dependent, we may say that SELECT clause of this INSERT is deterministic w/o ORDER BY.
    – Akina
    Commented Oct 22, 2022 at 11:08
  • @Akina But the order of the inserts does matter for the replica, no? How would the replica know, which id's it has to assign the inserted rows to be identical to the rows in replicated table? Remember that the target db table has an auto-increment id.
    – binford
    Commented Oct 22, 2022 at 11:13
  • @ErgestBasha I added the execution plans of only the SELECT as requested by you.
    – binford
    Commented Oct 22, 2022 at 11:14
  • @binford can you add the following index and update the results ALTER TABLE example_table ADD INDEX test (ref_sub_year,type,count,product_desc,product_category,ind_desc,manufacturer_country_name,updated_at); ? Commented Oct 22, 2022 at 12:07

0

Your Answer

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

Browse other questions tagged or ask your own question.