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 TABLEfor every table
- The size of each table
- The query
- value of
- 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?