Have a database with a varchar primary key. skyandrews said that it will load faster if I create an integer primary key instead. There are several tables relating to the varchar primary key. How can I edit all of the tables to make the database run faster?
here is the database I’m working on:
https://www.dropbox.com/scl/fo/lx6g7fq5vkuy5ehvhp4p7/AAGM7I5_ZKCmlbAKSacBlig?rlkey=dw9w58w8fd0t1fjtfh9tdnn76&st=iz7mhl4q&dl=0
These are instructions from Profile - skyandrews - Ask LibreOffice. as posted here: Table refresh on button push - #9 by goedible
Blockquote
1 Delete Relationships. Open the odb file from the original database folder, goto Tools / Relationships, delete all relations between the old Primary Key (oldPK) of the parent table (pTable) and all related old Foreign Keys (oldFK) of child tables (cTable). Save and close Relations! Save odb!
2 Create copy of pTable. In the LO Base Tables list, drag and drop pTable in an empty area below the Tables list. In the prompt, name like “pTable2”, select only the “Definition and data” option, then Create. Do Not create new primary key. Verify the new pTable2 appears in the Tables list and then open to verify it is properly populate. If it appears correct, close the table and save the odb!
3 Delete all pTable rows of data. Open pTable, select all rows (left click upper left corner header “block”), then right click the corner block and select “Delete rows”. The deletion may require some time, depending on the amount of data. When completed, close the empty pTable. Save odb, close the LO app, and then reopen the odb! (This clears LO cache in order to accomodate the following procedures adequately, preventing crashes)
4 Edit pTable. Select and edit the pTable to remove the Primary Key designation of the current primary field (oldPK) by right clicking its row marker, then deselect “Primary Key”. (The key icon should disappear) Save table! Then append an integer type field named like “ID”. Save and close the table! Save and close the odb! To simplify and avoid confusion, try giving primary key fields a name like “ID” (standard Caps), and related foreign keys like “ptable_id” (descriptive lowercase).
5 Edit “ID” field properties in db script file. In the original db folder, open the “mydb.script” file in a reliable text editor (notepad, notepad++, etc…). With editor’s “Word Wrap” OFF, find the line which begins with something like this: CREATE CACHED TABLE “pTable” - Select the line and then switch the editor’s “Word Wrap” to ON. Near the end of the selection is a segment that should be like this -
“ID” INTEGER - Carefully replace with -
"ID" INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 0) NOT NULL PRIMARY KEY
- Save and close script!
6 Append data into pTable from pTable2. Reopen the odb, then in the Tables list, drag and drop pTable2 directly onto pTable. In the prompt, name = “pTable”, select only the “Append data” option, then Create. Do Not create new primary key! After process completes, verify pTable now has complete data, including a valid “ID” primary key auto-increment field at the last column. Close table.
7 Delete pTable2 (optional). The pTable2 may now be deleted, or keep for backup. Upon deletion, save the odb! (Recommend delete to optimize odb)
8 Create new Foreign Key fields (ptable_id) in all related child tables. Edit each child table which is related to pTable and append an integer field named like “ptable_id”. Save and close child table! Then open the child table and move it to the side to remain in view. In the LO main menu bar, select Tools / SQL. Type, or copy and paste the following into the SQL command window -
UPDATE "cTable" SET "ptable_id" = SELECT "ID" FROM "pTable" WHERE "cTable"."oldFK"= "pTable"."oldPK"
(Replace table and field names with the applicable names in your own database) Execute! If there is an error, it will be indicated in the Status window. (The command will fail, stop) The command may take a few minutes, based upon the amount of data to query. Successful completion will be indicated in the Status window. Upon successful completion, refresh the previously opened cTable by clicking the table’s menu bar “Refresh” icon. (May need to select “Refresh”) Visually verify that the new foreign key field (ptable_id) was successfully populated. If so, save and close table! Before opening the next child table, the SQL command tool must be closed! Close the SQL tool and then open the next related child table and move to the side. Then reopen the SQL tool and repeat this step for each of the other related child tables! (To clear its cache, it is recommended the LO app be saved, then completely closed and reopened between each successful SQL command update)
9 Recreate relations between new pTable.ID primary key and all related cTables containing the new ptable_id foreign key. Goto LO main menu bar Tools / Relationships and recreate relations as previously described. With each relation, edit the Update property to “Update cascade” (right click the connecting line and select Edit). This will maintain the relationship even in the event the primary key integer is changed. Save and close relationship tool! Save the odb!
10 Cleanup and debug. Before deleting all of the unneeded oldPK and oldFK fields, they may be used to debug any conversion problems which may arrise while implementing the new PK and FK fields. Otherwise, the old fields should be deleted.
I tried:
UPDATE "plants” SET “id” = SELECT “ID” FROM “species” WHERE “plants”.“latin_name” = “species”.“Latin_name”
I got:
1: unknown token: at ./connectivity/source/drivers/jdbc/Object.cxx:173
it’s a problem with the quotes. This worked:
UPDATE "plants" SET "id" = SELECT "ID" FROM "species" WHERE "plants"."latin_name" = "species"."Latin_name"
UPDATE "cTable" SET "ptable_id" = SELECT "ID" FROM "pTable" WHERE "cTable"."oldFK" = "pTable"."oldPK"
Yes. I apologize for using smart quotes for the instructions. When copying from smart quotes into sql, all typographic quote marks must be edited. Also a child table’s foreign key should be named like “species_id”, not just “id”. A foreign key should be somewhat descriptive about which table holds the related “ID” primary key… hence “species_id” or “speciesID”.
- Here is an embedded version of the tables showing the new Integer primary and foreign keys, created in the split version using the 10 step suggestion.
EmbedLANdemo.odb (179.8 KB)
Using three backticks ` at the beginning and end of your block instead will give a verbatim copy of your spelling
No "change" here for 'quotes'
But I was told the autometed formatting is a feature…