SQLite performance (indexing) and Discussion to Switch to MongoDB

Hi there!

I’m using Cockpit with SQLite database, everything is going great and project goes live.
But recently, querying data with several collection link began to takes a lot of time.

I’ve tried create SQLite index with DB Browser for SQLite, try to query the data and it works great. But the query performance in Cockpit stays the same.

  • Does Cockpit supports SQLite indexing?
  • Is there anyway to add support for SQLite Index?

I’m also thinking to use MongoDB, with some concerns:

  • Does Cockpit support MongoDB Index?
  • I’ve been able to use transaction feature with SQLite and my project need this feature.
    how to use MongoDB Transaction (ACID) with Cockpit?
  • Any step by step guidelines to install Cockpit and MongoDB ?

How did you add index to columns in SQLite?
Did you do it via indexes on JSON expressions?

I’d say that the problem might be that when querying data, Cockpit executes custom function on each row instead of using database operations - so indexing doesn’t have any effect on queries.

MongoDB driver uses native queries so theoretically performance should be much higher.
MongoDB configuration is described in docs.

You could try my SQL driver addon but I must warn you that it’s not properly battle tested on production environments.

Hi @piotr-cz,

How did you add index to columns in SQLite?
Did you do it via indexes on JSON expressions?

Yes, I use indexing on JSON expression:

CREATE INDEX "test_id" ON "test" (
	json_extract(document, "$._id") asc
);

Oooh that’s right, after taking deeper look at the code, Cockpit use document_criteria function to filter data. I was thinking the problem caused by missing SQLite JSON1 support in pdo_sqlite extension.

Does it also stores data in single JSON column and what about Transaction support?
I’d like to try it if there is support for column indexing and Transaction,
I’m more familiar with RDBMS anyway :grin:

Yes, my addon uses single JSON column for data.

Unlike in SQLite driver, queries are performed via SQL so should utilize indexes on virtual columns as described here.

It doesn’t use transactions. The reason is that at the time of writing it I wasn’t sure at which level I should implement them.

However I’d recommend trying first MongoDB with Indexes.
I believe this is the first and primary storage driver that Cockpit used.
SQLite has been later on for simple databases when MongoDB was not available.