I'm having an doubt about coumpound indexes in my application. My db engine is Mysql 8.0 with INNODB and my question is the following:
My application has a model called Report with the follwing fields
date
, currency
, type
, company
,aggregator
and daily_value
. My applications consumes events from kafka where I receive a value
that represents a monetary transaction and the corresponding company
. I'm using django and I make the following query each time I receive an event:
SELECT company,currency,date,aggregator,type
WHERE company=passed_company,currency=passed_currency,
type=passed_type,aggregator=passed_aggregator,
date=RANGE(start_date,end_date)
to retrieve the given report for that day if it exists and increment the daily_value
property with the event value
or create the report for that day if it doesn't exist. Basically I do a get_or_create
in Django for the ones who are familiar with this framework.
My question is should I make a coumpound index on date, currency, type, company, aggregator
to optmimize my query and avoid possible deadlocks? I'm asking this because I will have multiple parallel consumers and I want to avoid concurrency issues.
Asked by Pedro Silva
(1 rep)
Jul 20, 2023, 09:52 AM
Last activity: Jul 20, 2023, 04:49 PM
Last activity: Jul 20, 2023, 04:49 PM