Sample Header Ad - 728x90

Question about multi fields index in MySQL

0 votes
2 answers
42 views
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_valueproperty 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