There is a process that generates statistics on transactions for different periods:
- 24 hours
- Weekly
- Monthly
- Quarterly
- Year
The query looks like this:
SELECT
DATE_FORMAT(CreatedTs, "%Y-%m-%d") `period`,
StatusId,
COUNT(idDeal) `count`
FROM Deals
WHERE CreatedTs BETWEEN "2021-01-01 00:00:00" AND "2021-01-31 23:59:59"
GROUP BY 1,
2;For a while, this query ran quite fast, but as the project evolved, the amount of data in the database increased significantly, which led to a decrease in its performance. What actions should be taken to improve query performance?
Modified column and table names to better fit Laravel conventions
- CreatedTs -> created_at
- period -> created_date
- StatusId -> status_id
- idDeal -> id
- Deals -> deals
and year is set to 2023 instead of 2021
SELECT
DATE_FORMAT(created_at, "%Y-%m-%d") `created_date`,
status_id,
COUNT(id) `count`
FROM deals
WHERE created_at BETWEEN "2023-01-01 00:00:00" AND "2023-01-31 23:59:59"
GROUP BY 1,
2;Replace COUNT(id) with COUNT(*)
SELECT
DATE_FORMAT(created_at, "%Y-%m-%d") `created_date`,
status_id,
COUNT(*) `count`
FROM deals
WHERE created_at BETWEEN "2023-01-01 00:00:00" AND "2023-01-31 23:59:59"
GROUP BY 1,
2;use DATE instead DATE_FORMAT
SELECT
DATE(created_at) `created_date`,
status_id,
COUNT(id) `count`
FROM deals
WHERE created_at BETWEEN "2023-01-01 00:00:00" AND "2023-01-31 23:59:59"
GROUP BY 1,
2;add created_date generated column
ALTER TABLE deals ADD COLUMN created_date DATE GENERATED ALWAYS AS (DATE(created_at)) STORED;SELECT
created_date,
status_id,
COUNT(id) `count`
FROM deals
WHERE created_date BETWEEN "2023-01-01" AND "2023-01-31"
GROUP BY created_date, status_id;add composite index key on created_date and status_id
ALTER TABLE deals ADD INDEX created_date_status_id_index (created_date, status_id)
- install dependencies with
comporser install - set your database connection on
.envfile - import
database/dump.sqlor (php artisan migrate && php artisan db:seed) - run benchmarks with
php artisan app:bench
React
Typescript
Django
Laravel
Facebook
Microsoft
Google
Alibaba
D3
Tencent