0

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 error-messages

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.

  • I don't think this is valid syntax: {d '2020-05-01'}; where did you read about that? – Rick James May 21 at 5:30
  • Looking at the error message, its not in the mariadb, in fact it looks like the SQL Server error message in this blog – danblack May 21 at 5:48
0
  • 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(*)?

| improve this answer | |
  • i use query on my post because for each or emp_id is different on production data i have may be more than 5000 emp_id. index you mean is like this create index table1_company_id on table1 (company_id) or create index table1_indexing on table1(company_id, period_code,paydate) – febry May 24 at 10:31
  • @febry - A "composite" (multi-column) index is not the same as multiple single-column indexes. I suggest making sure you have those two composite (3-column in both case) indexes on the table. If you currently have shorter indexes (eg, INDEX(company_id) _with the same leading set of column(s)_`, drop them; they will be redundant. – Rick James May 24 at 18:14
  • ok, i will try it. because i already have index with single columns. – febry May 25 at 2:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.