Re: Speed of query on tables containing blob depends on filesystem cache

[Periklis Venakis]

Hello,
thank you for your answer. The results for these to count(*) queries are 0, I used them in order to show that the delay is not due to the size of the result set.If I set the score to equal 100, then then result set contains 65k rows. In any case, here are all the results you asked for (which are after changing the table to innodb; I can give you myisam results as well if you prefer):

<code>
mysql> SELECT COUNT(*) FROM completed_tests ct where ct.status != 'deleted' and ct.status != 'failed' and score < 100;
+----------+
| COUNT(*) |
+----------+
| 0 |
+----------+
1 row in set (0.06 sec)

mysql> SELECT COUNT(*) FROM completed_tests ct where ct.status != 'deleted' and ct.status != 'failed' and score < 99;
+----------+
| COUNT(*) |
+----------+
| 0 |
+----------+
1 row in set (0.04 sec)

mysql> SHOW TABLE STATUS LIKE 'completed_tests'\G
*************************** 1. row ***************************
Name: completed_tests
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 791607
Avg_row_length: 3880
Data_length: 3071983616
Max_data_length: 0
Index_length: 26345472
Data_free: 7340032
Auto_increment: 117996
Create_time: 2012-03-01 11:08:37
Update_time: NULL
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options: row_format=COMPRESSED
Comment:
1 row in set (0.37 sec)

mysql> SHOW VARIABLES LIKE '%buffer%';
+-------------------------+----------+
| Variable_name | Value |
+-------------------------+----------+
| bulk_insert_buffer_size | 8388608 |
| innodb_buffer_pool_size | 8388608 |
| innodb_log_buffer_size | 1048576 |
| join_buffer_size | 131072 |
| key_buffer_size | 16777216 |
| myisam_sort_buffer_size | 8388608 |
| net_buffer_length | 16384 |
| preload_buffer_size | 32768 |
| read_buffer_size | 131072 |
| read_rnd_buffer_size | 262144 |
| sort_buffer_size | 2097144 |
| sql_buffer_result | OFF |
+-------------------------+----------+
12 rows in set (0.00 sec)

</code>

My testing system has 4G of memory (the production I'm not sure), but the 1st delay appears in any machine.

Using a compound index as you suggested would improve the speed (as it would read from the index), but in my case, I can't build such an index (I guess) because the actual query is more complicated. I stripped it down in this example for simplicity; here's the actual query:
<code>
SELECT ct.archive, ct.status, ct.score, ct.users_LOGIN, t.lessons_ID, t.content_ID, t.keep_best
FROM completed_tests ct,tests t
WHERE ct.status != 'deleted' and ct.status != 'incomplete' and t.id=ct.tests_ID and t.lessons_ID=10;
</code>
Do you think that the query above can be indexed in an efficient way? (even if I have remove the "users_LOGIN" column from the result set which is a varchar(255))

I have tried gzipping the contents of the blob but with no apparent improvements.