Avoid querying size of CONTENT_COLLECTION for /group/ or /attachment/

GENERAL

TESTING

GENERAL

TESTING

Description

Monitor slow query logs and you will see lots of

SET timestamp=1477424207;
select count(IN_COLLECTION) from CONTENT_COLLECTION where IN_COLLECTION = x'2F6174746163686D656E742F';
# Time: 161025 19:36:54
# User@Host: sakai[sakai] @ [127.0.0.1] Id: 1128516
# Query_time: 6.429790 Lock_time: 0.000043 Rows_sent: 1 Rows_examined: 58842
SET timestamp=1477424214;
select count(IN_COLLECTION) from CONTENT_COLLECTION where IN_COLLECTION = x'2F67726F75702F';

An index is already there. But there is probably no need to query for size here.

Activity

Show:

Sam Ottenhoff October 3, 2017 at 12:47 PM

@Matthew Buckett doesn't your explain show 190482 rows being scanned for the result? You never see this query end up in a slow query log? My guess is the indexes all load into memory for you, so it's still quick.

More info here: https://stackoverflow.com/questions/10976328/mysql-count-performance-on-very-big-tables

Sam Ottenhoff September 22, 2017 at 3:35 PM

I still see this query occasionally in our MySQL 5.6 (Aurora) slow query logs.

Matthew Buckett November 9, 2016 at 5:29 AM

In local test I don't see anywhere near that level of performance.

explain select count(IN_COLLECTION) from CONTENT_COLLECTION where IN_COLLECTION = '/user/';
# id, select_type, table, type, possible_keys, key, key_len, ref, rows, Extra
1, SIMPLE, CONTENT_COLLECTION, ref, CONTENT_IN_COLLECTION_INDEX, CONTENT_IN_COLLECTION_INDEX, 768, const, 190482, Using where; Using index

and performance wise I'm seeing the query take 0.04s uncached in production.