Mariadb – Query processor ran out of internal resources and could not produce a query plan” – MariaDB

execution-planmariadbqueryquery-performance

i have some query

SELECT  COUNT(loan_payment) as setcolumn
    FROM  table1
    WHERE  paydate between {d '2020-05-01'} AND {d '2020-05-31'}
      and  period_code = 'MONTHLY'
      AND  company_id = 11111
      AND  ( emp_id IN ('DO2071406','DO2073897','DO2073464','DO2071522',
                        'DO2071400','DO2071401','DO2071402','DO2071403','DO2073538',
                        'DO2074215','DO2074233','DO2070850','DO2071618','DO2070442',
                        'DO2071741','DO2071739','DO2071740','DO2071751')
              OR  emp_id IN ('DO2071406','DO2073897','DO2073464','DO2071522',
                        'DO2071400','DO2071401','DO2071402','DO2071403','DO2073538',
                        'DO2074215','DO2074233','DO2070850','DO2071618','DO2070442',
                        'DO2071741','DO2071739','DO2071740','DO2071751')OR emp_id IN ('DO2071406',
                        'DO2073897','DO2073464','DO2071522','DO2071400','DO2071401',
                        'DO2071402','DO2071403','DO2073538','DO2074215','DO2074233',
                        'DO2070850','DO2071618','DO2070442','DO2071741','DO2071739',
                        'DO2071740','DO2071751')OR emp_id IN ('DO2071406','DO2073897',
                        'DO2073464','DO2071522','DO2071400','DO2071401','DO2071402',
                        'DO2071403','DO2073538','DO2074215','DO2074233','DO2070850',
                        'DO2071618','DO2070442','DO2071741','DO2071739','DO2071740',
                        'DO2071751'
                          )

when i running i got error message like this

this is a limitation from where in () condition ??

because previously i put all emp_id like this

SELECT  COUNT(loan_payment) as setcolumn
    FROM  table1
    WHERE  paydate between {d '2020-05-01'} AND {d '2020-05-31'}
      and  period_code = 'MONTHLY'
      AND  company_id = 11111
      AND  ( emp_id IN ('DO2071406','DO2073897','DO2073464','DO2071522',
                        'DO2071400','DO2071401','DO2071402','DO2071403','DO2073538',
                        'DO2074215','DO2074233','DO2070850','DO2071618','DO2070442',
                        'DO2071741','DO2071739','DO2071740','DO2071751','DO2071406',
                        'DO2073897','DO2073464','DO2071522','DO2071400','DO2071401',
                        'DO2071402','DO2071403','DO2073538','DO2074215','DO2074233',
                        'DO2070850','DO2071618','DO2070442','DO2071741','DO2071739',
                        'DO2071740','DO2071751','DO2071406','DO2073897','DO2073464',
                        'DO2071522','DO2071400','DO2071401','DO2071402','DO2071403',
                        'DO2073538','DO2074215','DO2074233','DO2070850','DO2071618',
                        'DO2070442','DO2071741','DO2071739','DO2071740','DO2071751',
                        'DO2071406','DO2073897','DO2073464','DO2071522','DO2071400',
                        'DO2071401','DO2071402','DO2071403','DO2073538','DO2074215',
                        'DO2074233','DO2070850','DO2071618','DO2070442','DO2071741',
                        'DO2071739','DO2071740','DO2071751')

i got some error message.

Best Answer

OR does not optimize well; don't use that form.

One of these indexes will be optimal; add both:

INDEX(company_id, period_code, paydate)
INDEX(period_code, company_id, emp_id)

{d '2020-05-01'} can be simply '2020-05-01'

You might find this easier to deal with

    paydate >= '2020-05-01'
AND paydate  < '2020-05-01' + INTERVAL 1 MONTH

COUNT(loan_payment) checks for NULL; perhaps you want simply COUNT(*)?

Related Solutions

SQL Server Query Plan – Hints Causing Query Plan Issues

as I have seen on this answer when I add option(recompile) to my query it runs fine accepting the index hint:

SELECT   MAX(AC1.changeDate)   
         -- AC1.changeDate
          FROM [dbo].[applicationStateChange]  AS ac1  WITH(INDEX(FI_ASC_ChangeDate))  
          WHERE AC1.newStatus = 'PLC'  
OPTION (RECOMPILE)

Still when I run the same query without the option(recompile) I get the same error

Msg 8622, Level 16, State 1, Line 479 Query processor could not produce a query plan because of the hints defined in this query. Resubmit the query without specifying any hints and without using SET FORCEPLAN.

On my test environment however, I could afford running the following operation and after that no more problems with this filtered index:

ALTER DATABASE [JUNOCORE] SET PARAMETERIZATION SIMPLE;

to corroborate to my decision to do this in test I read the following articles:

SQL Server Simple and Forced Parameterization

Blitz Result: Forced Parameterization

Now I feel like changing my [JUNOCORE] database in LIVE also to simple parameterization

This then brings me to the following question:

what elements of the workload or query plan cache I should have a look to decide Simple or Forced Parameterization in my databases?

SQL – How to Use Outer Join with Extra Column Condition

You don't need subqueries. You only need to use both columns in the ON condition of the FULL JOIN:

SELECT
    -- t1.*,     -- adjust, keep only 
    -- t2.*,     -- needed columns
    COALESCE(t1.date, t2.date)
                  AS date, 
    COALESCE(t1.identifier, t2.identifier)
                  AS identifier, 
    t1.attribute1 AS a_attribute1,
    t1.attribute2 AS a_attribute2,
    t2.attribute1 AS b_attribute1,
    t2.attribute2 AS b_attribute2
FROM table1 AS t1 
     FULL JOIN table2 AS t2
     ON  t1. date = t2. date
     AND t1. identifier= t2. identifier
-- WHERE
--     (t1.date = '2000-01-01' OR t2.date = '2000-01-01')
 ;

If we want date and identifier combined in one column each, we can use COALESCE() function.

Best Answer

Related Solutions

SQL Server Query Plan – Hints Causing Query Plan Issues

SQL – How to Use Outer Join with Extra Column Condition

Related Question