0

I looked through the posts but cannot find an answer.
I inherited this application so the response is 5+ seconds
Server version: 10.2.25-MariaDB-log SUSE
I added an index on 'LFT' and 'RGT' which helps a lot (down from 40+ secondes)

###explain<br>
+------+-------------+--------------------+--------+---------------------------------------------------------------------------------------------------------------------+---------+---------+----------------------------+---------+-----------------------------+
| id   | select_type | table              | type   | possible_keys                                                                                                       | key     | key_len | ref                        | rows    | Extra                       |
+------+-------------+--------------------+--------+---------------------------------------------------------------------------------------------------------------------+---------+---------+----------------------------+---------+-----------------------------+
|    1 | SIMPLE      | information_object | ALL    | PRIMARY,information_object_FI_14,information_object_FI_16,information_object_FI_18,information_object_FI_19,lft,rgt | NULL    | NULL    | NULL                       | 7859078 | Using where; Using filesort |
|    1 | SIMPLE      | object             | eq_ref | PRIMARY                                                                                                             | PRIMARY | 4       | atom.information_object.id |       1 |                             |
+------+-------------+--------------------+--------+---------------------------------------------------------------------------------------------------------------------+---------+---------+----------------------------+---------+-----------------------------+


Below my query:

SELECT object.CLASS_NAME, object.CREATED_AT, object.UPDATED_AT, 
       object.ID, object.SERIAL_NUMBER, information_object.ID, 
       information_object.IDENTIFIER, information_object.PARTNO, 
       information_object.OAI_LOCAL_IDENTIFIER, 
       information_object.LEVEL_OF_DESCRIPTION_ID, 
       information_object.COLLECTION_TYPE_ID, 
       information_object.REPOSITORY_ID, 
       information_object.REGISTRY_ID, information_object.PARENT_ID, 
       information_object.DESCRIPTION_STATUS_ID, 
       information_object.DESCRIPTION_DETAIL_ID, 
       information_object.DESCRIPTION_IDENTIFIER, 
       information_object.SOURCE_STANDARD, 
       information_object.DISPLAY_STANDARD_ID, 
       information_object.FORMAT_ID, information_object.SIZE_ID, 
       information_object.TYP_ID, information_object.EQUIPMENT_ID, 
       information_object.LFT, information_object.RGT, 
       information_object.SOURCE_CULTURE, information_object.SHELF, 
       information_object.ROW, information_object.BIN, 
       information_object.IMPORT_ID 
FROM `object`, `information_object` 
WHERE information_object.LFT<13702908 
  AND information_object.RGT>13702909 
  AND information_object.ID=object.ID 
ORDER BY information_object.LFT ASC;

This query takes about 2 seconds
SELECT COUNT(*) FROM `information_object`, `object` WHERE information_object.LFT<13702908 AND information_object.RGT>13702909 AND information_object.ID=object.ID;

CREATE TABLE `information_object` ( `id` INTEGER NOT NULL, `identifier` VARCHAR(1024), `partNo` VARCHAR(1024), `oai_local_identifier` INTEGER NOT NULL AUTO_INCREMENT, `level_of_description_id` INTEGER, `collection_type_id` INTEGER, `repository_id` INTEGER, `registry_id` INTEGER, `parent_id` INTEGER, `description_status_id` INTEGER, `description_detail_id` INTEGER, `description_identifier` VARCHAR(1024), `source_standard` VARCHAR(1024), `display_standard_id` INTEGER, `format_id` INTEGER, `size_id` INTEGER, `typ_id` INTEGER, `equipment_id` INTEGER, `shelf` VARCHAR(50), `row` VARCHAR(50), `bin` VARCHAR(50), `move_permanent` INTEGER, `import_id` VARCHAR(1024), `lft` INTEGER NOT NULL, `rgt` INTEGER NOT NULL, `source_culture` VARCHAR(16) NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `information_object_U_1` (`oai_local_identifier`), KEY `lft`(`lft`), KEY `rgt`(`rgt`), CONSTRAINT `information_object_FK_1` FOREIGN KEY (`id`) REFERENCES `object` (`id`) ON DELETE CASCADE, INDEX `information_object_FI_2` (`level_of_description_id`), CONSTRAINT `information_object_FK_2` FOREIGN KEY (`level_of_description_id`) REFERENCES `term` (`id`) ON DELETE SET NULL, INDEX `information_object_FI_3` (`collection_type_id`), CONSTRAINT `information_object_FK_3` FOREIGN KEY (`collection_type_id`) REFERENCES `term` (`id`), INDEX `information_object_FI_4` (`repository_id`), CONSTRAINT `information_object_FK_4` FOREIGN KEY (`repository_id`) REFERENCES `repository` (`id`), INDEX `information_object_FI_5` (`parent_id`), CONSTRAINT `information_object_FK_5` FOREIGN KEY (`parent_id`) REFERENCES `information_object` (`id`), INDEX `information_object_FI_6` (`description_status_id`), CONSTRAINT `information_object_FK_6` FOREIGN KEY (`description_status_id`) REFERENCES `term` (`id`) ON DELETE SET NULL, INDEX `information_object_FI_7` (`description_detail_id`), CONSTRAINT `information_object_FK_7` FOREIGN KEY (`description_detail_id`) REFERENCES `term` (`id`) ON DELETE SET NULL, INDEX `information_object_FI_8` (`display_standard_id`), CONSTRAINT `information_object_FK_8` FOREIGN KEY (`display_standard_id`) REFERENCES `term` (`id`) ON DELETE SET NULL, INDEX `information_object_FI_9` (`format_id`), CONSTRAINT `information_object_FK_9` FOREIGN KEY (`format_id`) REFERENCES `term` (`id`) ON DELETE SET NULL, INDEX `information_object_FI_10` (`size_id`), CONSTRAINT `information_object_FK_10` FOREIGN KEY (`size_id`) REFERENCES `term` (`id`) ON DELETE SET NULL, INDEX `information_object_FI_11` (`typ_id`), CONSTRAINT `information_object_FK_11` FOREIGN KEY (`typ_id`) REFERENCES `term` (`id`) ON DELETE SET NULL, INDEX `information_object_FI_12` (`equipment_id`), CONSTRAINT `information_object_FK_12` FOREIGN KEY (`equipment_id`) REFERENCES `term` (`id`) ON DELETE SET NULL,
INDEX `information_object_FI_13` (`import_id`), INDEX `information_object_FI_14` (`lft`), INDEX `information_object_FI_15` (`rgt`), INDEX `information_object_FI_16` (`identifier`), INDEX `information_object_FI_17` (`parent_id`, `lft`, `rgt`), INDEX `information_object_FI_18` (`lft`, `rgt`), INDEX `information_object_FI_19` (`id`, `lft`, `rgt`) )Engine=InnoDB;

CREATE TABLE `object` ( `class_name` VARCHAR(255), `created_at` DATETIME NOT NULL, `updated_at` DATETIME NOT NULL, `id` INTEGER NOT NULL AUTO_INCREMENT, `serial_number` INTEGER default 0 NOT NULL, PRIMARY KEY (`id`) )Engine=InnoDB;

5
  • Test INDEX `information_object_FI_20` (`lft`, `rgt`, `id`) - maybe it will help slightly more... and think about spatial (RTREE) index by (`lft`, `rgt`). – Akina Apr 17 '20 at 10:53
  • information_object_FI_18 is effectively what the suggested information_object_FI_20 is as id being the primary key is implicitly at the end of secondary indexes. How much of the table does information_object.LFT<13702908 correspond to? and information_object.RGT>13702909? Look at forcing the index to information_object_FI_18 and observe the result. – danblack Apr 18 '20 at 5:27
  • With force index 1 row in set (0.93 sec) Without force index 1 row in set (4.92 sec) I will incorporate it into the code – JohanPiet Apr 18 '20 at 13:59
  • I added force index to the code. It speeds up a bit but still not usable. It went from 5 seconds to about 3 seconds. The system loops through at least 7 of these iterations adding to 35s down to 21s for screen to come back. – JohanPiet Apr 18 '20 at 14:23
  • SELECT count(*) FROM object, information_object` WHERE information_object.LFT<13702908 and information_object.ID=object.ID ORDER BY information_object.LFT ASC,information_object.LFT ASC;` +----------+ | count() | +----------+ | 6851459 | +----------+ 1 row in set (2 min 9.55 sec) SELECT count(*) FROM object, information_object` WHERE information_object.RGT>13702909 AND information_object.ID=object.ID ORDER BY information_object.LFT ASC,information_object.LFT ASC;` +----------+ | count() | +----------+ | 1184507 | +----------+ – JohanPiet Apr 20 '20 at 4:53
0

Sorry. LEFT-RIGHT schema design in inherently inefficient. Since you are using 10.2, I recommend you experiment with redesigning the schema so that it is "hierarchical" and use a "recursive CTE".

(No, I don't know for sure that the change will be any faster.)

Meanwhile, I expect that this is the index that it is using; I don't see anything better:

(`lft`, `rgt`)  -- in that order

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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