Fusion - performance database - possible missing indexes

[Tore Van Grembergen]

Hi,

I was doing some testing with fusio and noticed a high load on the database.

It looks like some indexes might be missing on the database.

I added below indexes and these seem to help improve performance.

ALTER TABLE `fusiodb`.`fusio_log`
ADD INDEX `category_date` (`category_id` ASC, `ip` ASC, `date` ASC) VISIBLE;

ALTER TABLE `fusiodb`.`fusio_routes`
ADD INDEX `category_status` (`category_id` ASC, `status` ASC) VISIBLE;

ALTER TABLE `fusiodb`.`fusio_rate`
ADD INDEX `status_id` (`status` ASC, `id` ASC) VISIBLE;

ALTER TABLE `fusiodb`.`fusio_routes`
ADD INDEX `status` (`status` ASC) VISIBLE;

Maybe something to check and evaluate ?

Kind regards

Tore

[Christoph Kappestein]

Hi Tore,

thanks for the info, we could indeed easily add those indexes to our schema.

How have you measured this performance, then I will also try to test and evaluate those indexes.

best regards

Christoph

[Tore Van Grembergen]

Dear Christoph,

Due to lack of time, I did not measure it scientificaly.

I noticed that when using fusio, the server running fusio would go to 100% usage.

I looked at it in more detail and noticed that the load mainly came from mysql.

I had a look at the queries that were sent to mysql and logged the onces that where not using indexes.

After applying the indexes the load dropped to 25% .

I hope this helps.

Kind regards

Tore

Op dinsdag 21 juni 2022 om 23:57:16 UTC+2 schreef christoph....@gmail.com:

[Christoph Kappestein]

Hi Tore,

thanks for the feedback and it is really good idea to check whether we can gain additional performance through those indexes,

I have also just created a PR s. https://github.com/apioo/fusio-impl/pull/27/files where I add those indexes, I will try and test a little bit but it looks indeed promising,

so thanks again for the feedback.

best regards

Christoph