We have a medium sized ecommerce website which is entirely custom built in using Java and MySQL database on AWS infrastructure. Over a period of past several years, our orders volume has grown substantially high and so the size of our database.
From past couple of months we are facing a problem of slow CRM (Admin application) especially reporting. These reports are primarily built around customers and our orders data. Few points to be noted about our system
- We have two web apps - storefront & backend CRM application
- Storefront is mostly cached so impact is not much visible there
- CRM and storefront are connected to a single database server having a single schema
- We have added several indexes in our customer, order and order_item tables to increase speed of queries used in reporting.
- These three tables are mostly frequently used for reporting purposes. Also website keep adding new records in these tables when a new user registers or places order.
- AWS RDS used as database server runs at average 35% CPU
- If I run these queries directly on RDS using MySql workbench then also it is slow.
I want to understand, how could I possibly improve performance of our CRM application. What is the key area where do I need to work on which will have substantial impact.
- Is it infrastructure? Better database server?
- Optimization in queries needed?
- Better indexing in tables needed?
Edit - Added sample query
For example, I am running following query on orders table to fetch orders count for a specific date. It takes around average 3 second of time to return data. I expect queries like this shouldn't take more than 1 second of time.
SELECT count(*) as Count FROM orders WHERE date(CONVERT_TZ(CreatedDate,'+00:00','+05:30')) = '2018-07-30' AND OrderStatusId IN (5,10,15) AND Deleted = 0
There are several such queries, which gets fired to build a report. Which eventually presents any report to user not less than 10-20 seconds
Edit - Added modified queries and their response time
As per the answers, I have tested this query without
convert_tz function and one without
convert_tz as well as
date. But results are not quite good. There is a gain of just 300 ms if I remove both
date functions.I tested it multiple times, and everytime this is the difference between these queries.
- Query 1 - time taken = 4.91 sec
SELECT count(*) as Count FROM orders WHERE date(CreatedDate) = '2018-07-30'
AND OrderStatusId IN (5,10,15) AND Deleted = 0;
- Query 2 - time taken = 1.72 sec
SELECT count(*) as Count FROM orders WHERE CreatedDate >= '2018-07-30 00:00:00' AND CreatedDate <= '2018-07-30 23:59:59' AND OrderStatusId IN (5,10,15) AND Deleted = 0;
- Query 3 - time taken = 2.02 sec
SELECT count(*) as Count FROM orders WHERE date(CONVERT_TZ(CreatedDate,'+00:00','+05:30')) = '2018-07-30' AND OrderStatusId IN (5,10,15) AND Deleted = 0;