Skip to main content

⚠️ Please note that this topic or post has been archived. The information contained here may no longer be accurate or up-to-date. ⚠️

Speeding up the catalog

Helmut Kaufmann
Good morning,
I love Capture One except for its performance with the catalogue. I have been looking at the database and there are actually surprisingly few indices over the data. I have created a bunch after looking at the database and I was wondering if any of you is willing to apply thm using sqlightbrowser or alike - there is nothing that can go wrong as you can always delete the indices. If any of you know what I am talking about, here are the index command:


CREATE UNIQUE INDEX HK_100 ON ZIMAGE(ZEXP_DATE, Z_PK);
CREATE UNIQUE INDEX HK_101 ON ZIMAGE(ZDISPLAYNAME, Z_PK);
CREATE UNIQUE INDEX HK_102 ON ZIMAGE(ZCAMERADISPLAYNAME, Z_PK);
CREATE UNIQUE INDEX HK_103 ON ZIMAGE(ZIMAGEFILENAME, Z_PK);
CREATE UNIQUE INDEX HK_104 ON ZIMAGE(ZIMAGELOCATION, Z_PK);
CREATE UNIQUE INDEX HK_105 ON ZIMAGE(Z_PK, ZIMAGEFILENAME);
CREATE UNIQUE INDEX HK_106 ON ZIMAGE(Z_PK, ZIMAGELOCATION);
CREATE UNIQUE INDEX HK_107 ON ZIMAGE(Z_PK, ZEXP_DATE);
CREATE UNIQUE INDEX HK_108 ON ZIMAGE(Z_PK, ZDISPLAYNAME);

CREATE UNIQUE INDEX HK_200 ON ZSTACK(ZCOLLECTION, ZPICKEDIMAGE, Z_PK);
CREATE UNIQUE INDEX HK_201 ON ZSTACK(Z_PK, ZCOLLECTION, ZPICKEDIMAGE);

CREATE UNIQUE INDEX HK_300 ON ZCOLLECTION(Z_PK, ZNAME, ZPARENT);

CREATE UNIQUE INDEX HK_400 ON ZPATHLOCATION(Z_PK, ZMACROOT, ZRELATIVEPATH, ZVOLUME);

CREATE INDEX HK_500 ON ZSTACKIMAGELINK(ZSTACK, ZIMAGE);
CREATE INDEX HK_501 ON ZSTACKIMAGELINK(ZIMAGE, ZSTACK);

CREATE UNIQUE INDEX HK_600 ON ZVARIANT(ZIMAGE, Z_PK, ZDEFAULTLAYER);
CREATE UNIQUE INDEX HK_601 ON ZVARIANT(ZIMAGE, Z_PK, ZADJUSTMENTLAYER);
CREATE UNIQUE INDEX HK_602 ON ZVARIANT(Z_PK, ZIMAGE, ZDEFAULTLAYER);
CREATE UNIQUE INDEX HK_603 ON ZVARIANT(Z_PK, ZIMAGE, ZADJUSTMENTLAYER);

CREATE UNIQUE INDEX HK_700 ON ZVARIANTLAYER(Z_PK, ZVARIANT);
CREATE UNIQUE INDEX HK_701 ON ZVARIANTLAYER(ZVARIANT, Z_PK);


The following will delete all of them:

DROP INDEX HK_100;
DROP INDEX HK_101;
DROP INDEX HK_102;
DROP INDEX HK_103;
DROP INDEX HK_104;
DROP INDEX HK_105;
DROP INDEX HK_106;
DROP INDEX HK_107;
DROP INDEX HK_108;
DROP INDEX HK_200;
DROP INDEX HK_201;
DROP INDEX HK_300;
DROP INDEX HK_400;
DROP INDEX HK_500;
DROP INDEX HK_501;
DROP INDEX HK_600;
DROP INDEX HK_601;
DROP INDEX HK_602;
DROP INDEX HK_603;
DROP INDEX HK_700;
DROP INDEX HK_701;


I'd appreciate feedback, especially from people with large collections (for smart albums, I have the impression that the database is searched record by record with no index being used...)

Thanks,
Mercator
0

Comments

6 comments

Date Votes
  • paintbox
    I'm willing to try it, but am very ignorant on how its done.
    I just downloaded DB Browser for SQLite.
    Walk me through the steps.
    0
  • Helmut Kaufmann
    Good evening
    It is quite easy. Start the program, open the database file, which should end with .cocatalogdb. Go to the Execute SQL tab, Paste the first set of commands in, press the icon that looks like a triangle. Press Write Changes. Close the database. That is it.

    If you are on a Mac, you might not be able to open the .cocatalogdb as the database is in a package (it is the file you can click on that will start CO and load it). In that case, right.click on the package, copy the file ending on .cocatalogdb to the Desktop, and apply the steps above with the file on the Desktop. Then copy it back from the Desktop to the package.

    If you feel uncomfortable that you do something wrong, copy the whole packe to a safe location before applying the above steps.
    0
  • paintbox
    I tried it and didn't see any difference.
    However while looking for the catalog, I did notice that it resides outside the Capture One file.
    Not sure if that matters.
    0
  • jlarocco
    I've made the changes and will be trying it out.

    My first impression is that jumping between different smart collections and user collections seem a little snappier. Jumping back to "All Images" is still painfully slow, but scrolling through the browser and switching to random images seems a little faster once it loads.
    0
  • Helmut Kaufmann
    [quote="jlarocco" wrote:
    I've made the changes and will be trying it out.

    My first impression is that jumping between different smart collections and user collections seem a little snappier. Jumping back to "All Images" is still painfully slow, but scrolling through the browser and switching to random images seems a little faster once it loads.

    Yes, the All Images is indeed painful. I guess that it virtuall loads all information into memory and that takes ages. Besides that, please, let me know any improvements you see. I can only try and make assumptions on how the query looks like and then create index structures.
    0
  • Helmut Kaufmann
    [quote="Jimmy D Uptain" wrote:
    I tried it and didn't see any difference.
    However while looking for the catalog, I did notice that it resides outside the Capture One file.
    Not sure if that matters.

    Hi,
    What do you mean by "outside the CO file"?
    Regards,
    Mercator
    0

Post is closed for comments.