How to build reports for an ecommerce webapp without slowing it down

Question

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 convert_tz and date functions.I tested it multiple times, and everytime this is the difference between these queries.

1. 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;

2. 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;

3. 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;

Answer 1

Since you noted that your queries run slow when using the MySql Workbench, that's the first place to start.

In Sql Server, it's possible to view the query plan for a query and see exactly how the database will process it. Furthermore, you can check to see which index (if any) is used.

In the case of your sample query, your WHERE clause contains a calculation CONVERT_TZ(CreatedDate...). Sql Server would see this and resort to a table scan - it would read every record in the table, do this conversion, then filter accordingly. That's incredibly slow if you have a lot of records.

If possible, add a new field to that table CreatedDateUTC and do the calculation once. You'll be able to create an index on that field afterwards.

In other words, start with your last two bullets

Optimization in queries needed?
Better indexing in tables needed?

Answer 2

The standard answer to the problem of reports, which by their nature are slow to calculate is to run them against a data warehouse server rather than the transactional database.

This requires that you export your data out of your transactional DB. Normally this can be done as a continuous process.

A data warehouse database is structured differently to allow for fast report style queries and, as its a different server, queries against it wont affect the performance of your application.

However! your sample SQL seems pretty simple. It is probably slow due to the conversion of each rows date to a particular timezone. This is easily solved by the standard practice of storing dates as UTC.

It could be that some simple optimisation of your queries will enable you to run your reports against your transactional DB, or a simple replication of your Database to a failover/reporting server