Query is extremely slow after ALTER and deploying of updates
10 times as slow?  And if you ran it again, it would be back to normal?

That is normal.  Before the big ALTER, all the data for that query was cached in RAM.  Then the ALTER bumped most of it out.  After that, the query had to go to disk, hence 10x slower.  Running again should see it in cache and be fast again.

If this does not fit with what you are seeing, please provide the following so we can dig deeper:


SHOW CREATE TABLE for each table
The size of each table
The query
Value of innodb_buffer_pool_size
The ALTER
Are the JOINs 1:1?  If so, there is a probably a significant rewrite to speed it up, even the 1-2 seconds.


Notice that the query must read all of the rows from all 3 tables, and put all the data into a temp table for sorting.

You don't seem to need LEFT, can you remove it?

Possible indexes (pending seeing the above info):

block -- already has PRIMARY KEY(id)?
cache_vatno:  INDEX(vatno)
data_voucher:  INDEX(time, id)


If the joins are 1:1?  Assuming they are, give this rewrite a try:

SELECT  SQL_CALC_FOUND_ROWS dv.id,dv.client_id,
        ( SELECT b.name FROM block b WHERE b.id=dv.block_id ) AS block_name,
        dv.is_ocr_pending,dv.time,dv.label,dv.is_pdf_broken,dv.is_pdf_scan,
        dv.is_pic,dv.file_sha1,dv.file_ext,dv.file_ext_thumb,
        dv.file_size,dv.file_size_original,dv.num_pages,dv.dpi,
        dv.ocr_confidence,dv.ocr_recall_id,dv.ocr_vatno,
        ( SELECT cv.name FROM `cache_vatno` cv
              WHERE cv.vatno=dv.ocr_vatno ) AS ocr_vatno_name,
        dv.ocr_vatno_country,dv.ocr_is_vatno_verified,dv.ocr_invoice_id_,
        dv.ocr_invoice_time,dv.ocr_invoice_time_due,dv.ocr_fi_type,
        dv.ocr_fi_payment_id_,dv.ocr_is_fi_payment_verified, dv.ocr_fi_creditorno,
        dv.ocr_bank_code,dv.ocr_bank_code_id,dv.ocr_is_bank_code_verified,
        dv.ocr_bank_account,dv.ocr_is_bank_account_verified,dv.ocr_bank_iban,
        dv.ocr_is_bank_iban_verified,dv.ocr_bank_swift,dv.ocr_bank_swift_id,
        dv.ocr_is_bank_swift_verified,dv.ocr_products_pattern,
        dv.ocr_total,dv.ocr_is_total_verified,dv.ocr_vat,dv.ocr_currency
    FROM  `data_voucher` dv
    ORDER BY  dv.time DESC,dv.id DESC
    LIMIT  0,25


Rationale:  I turned the JOINs into subqueries, thereby making it obvious that things are 1:1, and letting the Optimizer focus on just the one table (dv).  This should allow it to use INDEX(time, id) to handle both the ORDER BY and the LIMIT, thereby stopping after 25 rows.

Unfortunately, that still leaves the processing of SQL_CALC_FOUND_ROWS.  It must proceed to count the rest of the rows in that index.  So, removing the CALC may speed up the query signficantly.  Does the UI really need it?

...