Query is extremely slow after ALTER and deploying of updates I run a test server and a production server..
When I do some changes on the db structure on the test server I save all the SQL queries to later run them on the production server when I deploy the updates
Today I deployed some updates on the production server, but at least one query is now so slow that it will timeout (it takes several minutes).. It used to take 1-2 sec
On the test server the same query still runs smooth as it used to
query
SELECT dv.id,dv.client_id,b.name 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,cv.name 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
LEFT JOIN `block` b ON b.id=dv.block_id
LEFT JOIN `cache_vatno` cv ON cv.vatno=dv.ocr_vatno
ORDER BY dv.time DESC,dv.id DESC
LIMIT 0,25

It doesn't make sense that it timeout on one server but doesn't on the other.. When I dump the table structures the query affect the dumps are exactly the same?
If I exclude the line with LEFT JOIN cache_vatno cv ON cv.vatno=dv.ocr_vatno and remove the field cv.name ocr_vatno_name from the select clause it runs and completes as it did before the deploy
I don't know what to do?
I have now used perconas extrabackup to dump the production database and deploy it on the test server.. Now the test server has the same issues?
I can browse the table cache_vatno in phpmyadmin without problems
The table data_voucher has over 100k rows
Have tried to restart mysql
Addeing EXPLAIN to the query give this
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?

...