Redis Cache for Database Query Performance

@JussiMannisto Thank you for the response. Yes we have complex data retrieval queries, which will be framed based on lot of conditions. we tried to optimize queries by creating indexes and stored procedures. I am still trying to see if there is any other ways from lumen can help to lift the performance other than caching final result set using redis.

@arunasrim Lumen is just a light-weight API version of Laravel. It can improve performance on the PHP side but can't do anything special for your database queries.

If you have queries with varying parameters, then a result set cache may be of limited help. Indexes are crucial, but you probably already know that. If those don't solve your problems, there are many things you can do, e.g.:

1. Optimize your database queries.
2. Re-think your database structure. Sometimes adding redundant data or aggregate tables can help if they allow you to avoid heavy operations. Especially consider this if your queries contain aggregate functions such as count() or sum().
3. Use a search engine and search against its index rather than the DB directly. This is might be the most effective option for you. There are many options available, such as Elasticsearch, Sphinx, and Solr.
4. Create replicate database server(s) if you don't have any. Use the replicate servers for all your read queries and only use the primary server for write operations. This would spread the load between servers but won't directly address the issue of slow queries.

Do you have an SQL database? The EXPLAIN command is an excellent tool for analyzing your queries.

@JussiMannisto Thanks a lot for the tips, we have taken care of few tips you mentioned. let me see if the replication things works or not in our case.

@arunasrim I was a bit hesitant to add replication as a suggestion. Remember that it won't make your queries faster on its own. It may help if your queries are causing a lot of load on the primary server.

If you're just trying to improve the speed of e.g. a web API, I'd try a search engine. It can drastically improve performance and it's easier to set up than replication.