MySQL Forums
Forum List  »  Performance

Re: Speed of query on tables containing blob depends on filesystem cache
Posted by: Rick James
Date: February 29, 2012 11:59PM

Please provide
SELECT COUNT(*) FROM completed_tests ct where ct.status != 'deleted' and ct.status != 'failed' and score < 100;
SELECT COUNT(*) FROM completed_tests ct where ct.status != 'deleted' and ct.status != 'failed' and score < 99;
SHOW TABLE STATUS LIKE 'completed_tests'\G
SHOW VARIABLES LIKE '%buffer%';
How much RAM do you have?

Consider moving the deleted and failed rows to another table.

Did you change the caches when you changed the engine? See
http://mysql.rjweb.org/doc.php/memory

`failed` and `deleted` are not in the CREATE TABLE you provided.

Do you have other SELECTs you would like to optimize at the same time? (Speeding up one may mess up another.)

Learn about "compound indexes", and add
INDEX(status, failed, score)

Consider compressing the blob.

> without removing the blob column from the table
So, you don't want me to suggest "vertical partitioning".

In MyISAM, especially, your table scan has to step over 117996 cow paddies; this takes time.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Speed of query on tables containing blob depends on filesystem cache
1561
February 29, 2012 11:59PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.