Using a query with a table and a View, however if i change the where column the speed drastically changes. What can be the problem? its quite hard to google "performance changes on where clause" since all exemples are not even close to this.

SELECT ADRE.*
FROM INVOICE N
INNER JOIN ENTRY_INVOICE NAF
     ON NAF.COMPANY = N.COMPANY
     AND NAF.ID_INVOICE = N.ID_INVOICE
INNER JOIN UV_ENTRY_ALL ADRE
    ON ADRE.COMPANY = NAF.COMPANY
    AND ADRE.ID_ENTRY = NAF.ID_ENTRY
WHERE
    NAF.COMPANY = 1
    /*
    AND NAF.ID_INVOICE = 113806
        => 40 SECONDS
        
    AND  NAF.ID_ENTRY = 387473
        => 0,6 SECONDS
        
    AND EXISTS (SELECT 1 FROM ENTRY_INVOICE WHERE COMPANY = 1 AND ID_INVOICE=113806)
        => 1,6 SECONDS
    */

INVOICE_ENTRY IT HAS AT MOST 2 RECORDS FOR EACH INVOICE.

PLAN EXPLANATION https://exists-stack.tiiny.site/

As questioned, even if remove the table INVOICE, and select especific columns, it wont change the speed.

CodePudding user response：

I would change the filtering criteria to WHERE N.COMPANY = 1 to nudge the optimizer to use that table as the driving table. The query could look like:

SELECT ADRE.*, N.*
FROM INVOICE N
INNER JOIN ENTRY_INVOICE NAF
     ON NAF.COMPANY = N.COMPANY
     AND NAF.ID_INVOICE = N.ID_INVOICE
INNER JOIN UV_ENTRY_ALL ADRE
    ON ADRE.COMPANY = NAF.COMPANY
    AND ADRE.ID_ENTRY = NAF.ID_ENTRY
WHERE
    N.COMPANY = 1 -- changed here

Once that is changed, the query could benefit from the following indexes:

create index ix1 on invoice (company);

create index ix2 on entry_invoice (company, id_invoice);

create index ix3 on uv_entry_all (company, id_entry);

Keep in mind that any extra search criteria may impact performance. In particular, I ignored the predicates in comments at the end of the query.

CodePudding user response：

I would rewrite the query based on the primary table in question you are trying to get your data from based on the criteria. Make sure to have an index that will best help in those situations. THEN Join to the other tables. Also, if dealing with the condition of a specific invoice, I would just add that to the JOIN clause as well. Hopefully the engine will get best match for criteria and run with that. In this case, your Entry Invoice table would have TWO indexes... one based on the company invoice, the other based on company id entry.

As for your invoice table, that is not even being used within your output and would otherwise just be like that of a lookup table, so I added that as a second join

Table           Index
Entry_Invoice   ( Company, ID_Invoice )
Entry_Invoice   ( Company, ID_Entry )
Invoice         ( Company, ID_Invoice )
UV_Entry_All    ( Company, ID_Entry )

SELECT 
        ADRE.*
    FROM 
        ENTRY_INVOICE NAF
            JOIN UV_ENTRY_ALL ADRE
                ON NAF.COMPANY = ADRE.COMPANY
                AND NAF.ID_ENTRY = ADRE.ID_ENTRY
            -- you dont actually NEED the invoice table here
            INVOICE N
                ON NAF.COMPANY = N.COMPANY
                AND NAF.ID_INVOICE = N.ID_INVOICE
    WHERE
            NAF.COMPANY = 1
        AND
        /*
            NAF.ID_INVOICE = 113806
                 vs
            NAF.ID_ENTRY = 387473
        
            -- dont think this would even be necessary any more
            EXISTS (SELECT 1 FROM ENTRY_INVOICE WHERE COMPANY = 1 AND ID_INVOICE=113806)
        */

CodePudding user response：

UV_ENTRY_ALL Was a View that returned all payments from invoice however i tought removing some tables would make it perform better, and it does, but i need to filter the exact entry.
By adding back on each Union (union all) ENTRY_INVOICE and returning "ID_INVOICE" i added it on the Join and now works like a charm.
on the other hand i lost Performance while i run UV_ENTRY_ALL standalone

SELECT ADRE.*
FROM INVOICE N
INNER JOIN ENTRY_INVOICE NAF
    ON NAF.COMPANY = N.COMPANY
    AND NAF.ID_INVOICE = N.ID_INVOICE
INNER JOIN UV_ENTRY_ALL ADRE
   ON ADRE.COMPANY = NAF.COMPANY
   AND ADRE.ID_ENTRY = NAF.ID_ENTRY
   AND ADRE.ID_INVOICE = NAF.ID_INVOICE
WHERE
   NAF.COMPANY = 1 AND NAF.ID_INVOICE = 113806