How to build a cache layer on top of an e-commerce database

We have a platform that supports e-commerce stores; it is like a lighter version of Amazon or Flipkart with some 100–200 stores. Our MariaDB database has reached more than one million rows, and queries have started slowing down. So we decided to build a cache on top of MariaDB. We have a few requirements that our cache should support.

Cache requirements

1. should support SQL queries
2. should have good support for Java and Python
3. Should be in-line with our tech stack

Our tech stack

1. Backend - Spring Boot & FastApi
2. ORM - Hibernate
3. Database - MariDB

We tried using SQLite as an embedded cache with CDC to sync changes, but it didn’t work well for us, Java support for SQLite is not great, and the JDBC driver we used has some memory leaks. So we want to redesign the cache that we have.

1. What ar开发者_JAVA百科e some of the best practises we should follow?
2. Is there anyone who has solved similar problem?If so, what was the design you used?
3. What cache should I use?