mysql – The query is extremely slow after ALTER and posting updates

10 times as slow? And if you did it again, would it be normal again?

That is normal. Before the big one TO CHANGEAll data for this query has been cached in RAM. Then that is TO CHANGE most of it expelled. After that, the query had to go to the hard drive, so 10x slower. Running again, it should be in the cache and be fast again.

If that does not match what you see, please include the following to dig deeper:

  • Show SHOW CREATE TABLE for every table
  • The size of each table
  • The query
  • value of innodb_buffer_pool_size
  • Age
  • Are the JOINs 1: 1? If so, there is probably one significant To accelerate it, even the 1-2 seconds.

Note that the query must read all rows from all 3 tables and insert all the data into a temporary table for sorting.

You do not seem to need it LEFT, can you remove it?

Possible indexes (pending the above information):

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

If the links are 1: 1? Assuming they are, try this description:

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
WO 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

Reason: I converted the JOINs into subqueries, which made it clear that things are 1: 1, and the optimizer can focus on just one table (dv). This should allow use INDEX (time, ID) both with the SORT BY and the BORDERand stops after 25 rows.

Unfortunately, that leaves the processing of SQL_CALC_FOUND_ROWS, It must continue to count the rest of the lines in this index. How to remove the CALC can Accelerate the query considerably. Does the user interface work? Really need?