How can I Optimize the Sql query with unindex date column?

Question

I need to Optimize this query as creationDate is not index column . So it is taking time . Below is the query :-

SELECT 
    quoteId as 'ID of the quote',
    requestType as 'Quote Type',
    type as 'New or Change',
    taskName as 'Quote Status',
    currency.currencyCode as 'Currency Code',
    customer.name as 'Customer Name(Contracting Party)',
    quote.contractingParty_customerId as 'Customer IC01(Contracting Party)',
    customer.salesCountryValue as 'Customer sales country',
    item.fpc_financeProductCodeId as 'fpc',
    opt.number as "option", 
    opt.pricingMonthlyAmount as "Monthly existing Charges(Eur).", 
    opt.pricingOneOffCharges as "Total One-off Charges(EUR)", 
    opt.customerLabel as "Customer Label",
    item.property as "service infomration"
FROM
pqto01.qto_quote quote inner join   
pqto01.qto_product_item item on quote.product_productId = item.productItems_productId  inner join 
pqto01.qto_option opt on opt.options_productItemId = item.productItemId inner join 
pqto01.qto_customer customer on  customer.customerId = quote.contractingParty_customerId inner join 
pqto01.qto_currency currency on quote.pricingCurrency1_currencyId = currency.currencyId 
where  quote.creationDate between ('2020-03-01' and '2020-03-10') and
(opt.pricingMonthlyAmount = 0 and opt.pricingOneOffCharges = 0);

If i am running the query without creationDate then it is taking less time but with creationDate in where it is taking more than 30 sec in mysql . So , any way i can make it faster without indexing the creationDate column. Database i m using is my sql. Thank you.

Answer 1

opt may benefit from this composite index:

INDEX(pricingOneOffCharges, pricingMonthlyAmount)