Strapi API return slowly on table with 500k records. How to make it faster?

Questions and Answers Database-SQL
1
System Information
  • Strapi Version: 4.1.5
  • Operating System: Ubuntu Linux
  • Database: MySQL 8.0.27
  • Node Version: v14.18.1
  • NPM Version: 8.5.4
  • Yarn Version:

Hi, I have a collection with about 500k items. It call Rest API and it return data slowly. It take 6.39s. How can I make it faster? Thanks!

My collection schema:

{
  "kind": "collectionType",
  "collectionName": "projects",
  "info": {
    "singularName": "project",
    "pluralName": "projects",
    "displayName": "Project",
    "description": ""
  },
  "options": {
    "draftAndPublish": true
  },
  "pluginOptions": {},
  "attributes": {
    "name": {
      "type": "string",
      "required": true
    },
    "alias": {
      "type": "string",
      "unique": true,
      "required": true
    },
    "description": {
      "type": "richtext"
    },
    "download": {
      "type": "string"
    },
    "logo": {
      "type": "string"
    },
    "source": {
      "type": "string"
    },
    "screenshots": {
      "type": "json"
    },
    "project_categories": {
      "type": "relation",
      "relation": "oneToMany",
      "target": "api::project-category.project-category"
    },
    "project_reviews": {
      "type": "relation",
      "relation": "oneToMany",
      "target": "api::project-review.project-review",
      "mappedBy": "project"
    },
    "review_count": {
      "type": "integer"
    },
    "review_score": {
      "type": "decimal"
    },
    "version": {
      "type": "string"
    },
    "download_count": {
      "type": "integer"
    },
    "developer_name": {
      "type": "string"
    },
    "developer_link": {
      "type": "string"
    },
    "faqs": {
      "type": "relation",
      "relation": "oneToMany",
      "target": "api::faq.faq",
      "mappedBy": "project"
    },
    "review_1star_percent": {
      "type": "integer"
    },
    "review_2star_percent": {
      "type": "integer"
    },
    "review_3star_percent": {
      "type": "integer"
    },
    "review_4star_percent": {
      "type": "integer"
    },
    "review_5star_percent": {
      "type": "integer"
    }
  }
}
1 Like
2

Are you populating any deep relations when calling this endpoint? Doing so can cause significant slowdowns when querying lots of records. If so, best way around that is to create a custom api endpoint that compiles the relations server side without the use of api filtering (Filtering for REST API - Strapi Developer Docs)

3

No, I don’t populate any relations.

Here is my Rest API URL request: api/projects?pagination[page]=2&pagination[pageSize]=50&sort[0]=review_count:DESC

  • I’ve tried add column review_count to index in mysql and now it faster before but it spent 3.41s for result
  • I’ve tried remove sort params: api/projects?pagination[page]=3&pagination[pageSize]=50
    → 2.56s
  • Changed page size to 25: api/projects?pagination[page]=3&pagination[pageSize]=25
    → 2.36s
  • Remove all params: /api/projects
    → 2.54s

My server have 8GB RAM and it free about 2GB RAM when testing.
My table index:

+----------+------------+---------------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table    | Non_unique | Key_name                  | Seq_in_index | Column_name   | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+----------+------------+---------------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| projects |          0 | PRIMARY                   |            1 | id            | A         |      302266 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| projects |          1 | projects_created_by_id_fk |            1 | created_by_id | A         |           1 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| projects |          1 | projects_updated_by_id_fk |            1 | updated_by_id | A         |           1 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| projects |          1 | idx_projects_name         |            1 | name          | A         |      302341 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| projects |          1 | idx_projects_review_count |            1 | review_count  | A         |         390 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
+----------+------------+---------------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+

Can I make load time < 1s ? Thanks for your help!

4

You can make a test in the Query files of your content-type to check is it slow because of strapi itself or the database? is your database on the same server? , and if it is have you tried testing this when there are no other requests on the database?
one of the solutions I implemented before on limited resources was to split my table into max of 100k tables and have a mapping table to know which table I need to request depending on pagination.

5

Hi Nebras!

Thanks for your help. My database on the same server. My project is development and it have a few visitor daily. I think it slow from database.

You can make a test in the Query files of your content-type to check is it slow because of strapi itself or the database?

Please kindly provide me how to test it? I not sure how to get SQL query when access API.

I tried: show full processlist; on mysql when API loading and see a query executing is:
select t0.* from projectsast0 where (t0.published_atis not null) order byt0.review_count DESC limit 50 offset 453800

And I tried run this query on mysql it need (5.05 sec) to finish.

Some time I see a query: select count(*) as countfromprojectsast0 where (t0.published_at is not null) limit 1.
It require 1.02 sec to finish. I think it run after main query to show total items on meta.

Total it take 6-7 sec to do all query. I see time on browser return api is 6.62 sec. I think select t0.* will slow because it select all fields. I think if it select from index columns before select all fields will make query faster.

I tried a query select id only (because id is primary key): select t0.id from projectsast0 where (t0.published_atis not null) order byt0.review_count DESC limit 50 offset 453800;
it take 1.35 sec for result.

After that select by where in list ID: SELECT * FROM projects WHERE ID IN (267250,267251,267252,267254,267255,267257,267258,267260,267262,267264,267265,267266,267267,267270,267271,267273,267274,267275,267277,267279,267280,267281,267283,267285,267289,267290,267291,267295,267296,267297,267298,267300,267301,267302,267303,267304,267306,267307,267309,267310,267311,267315,267316,267318,267319,267320,267321,267322,267323,267324);
This query is very fast (0.00 sec)
=> But this way only change from Strapi core.

I tried other solution:

I created a new collection named is project-sorts it only have a review_count field (for sort data) and a relationship to projects collection.
api/project-sorts?pagination[page]=9072&pagination[pageSize]=50&sort[0]=review_count:DESC
→ it take 1.34s

and I need to access fields on projects I need to populate relationship:
api/project-sorts?pagination[page]=9072&pagination[pageSize]=50&sort[0]=review_count:DESC&populate=*
→ it take 1.33s ( I think it is good)

The before Rest API URL is:
api/projects?pagination[page]=9072&pagination[pageSize]=50&sort[0]=review_count:DESC
→ it take 9.89s

6

can we continue this discussion here? We are facing the same issue. For 200k+ entries, things get so slow… it is impossible to work with Strapi. How can we improve that? How can I manually add DB indices, that don’t get removed by strapi on start …

7

Faced same issue with sqlite. When having content type with many records, now more that 1 million. Even Admin refuses to work and entities with less data. Filtering and queries respond with more than 10 seconds on NVMe drive.

So basically having a table with more 1M rows will sow down other entites and admin as well.

8

I could speed things up by creating a custom plugin, that is using the bootstrap file to create several database indices. But when using SORT in the REST API I still get slow results - testing different database indices on the sorted fields doesn’t bring the expected results, unfortunately. I get the feeling, that Strapi is somehow breaking things when sorting results.