SQL CACHE Not used. When it should be?

[James Barwick]

I know it can be difficult to anticipate variable data when determining if a query will hit the SQL_CACHE or not. So I ask this cautiously and to get some feedback. Perhaps you can point me to a different way.

Setup:

Query_Cache_Type=2

The following query does not hit the cache, and I expected that it would (or want that it will):

SELECT SQL_CACHE * FROM MYUSERTABLE WHERE NAME=SUBSTRING_INDEX(USER(),'@',1);

The following query does hit the cache:

SELECT SQL_CACHE * FROM MYUSERTABLE WHERE NAME=SUBSTRING_INDEX('NAME@%','@',1);

Is there ANY way to do what I'm trying to do here?

In the end, what I'm really trying to do is (assume the grants are right):

CREATE DEFINER='reader'@'localhost' SQL SECURITY DEFINER VIEW schema1.MYUSERTABLE AS SELECT SQL_CACHE * FROM baseschema.MYUSERTABLE WHERE NAME=SUBSTRING_INDEX(USER(),'@',1);

Sorry for posting in this Forum, but I've racked my brain trying to determine which forum to post this question. If there is a different way to implement user 'row level security' within MySQL, point the way.

This works just fine, but I'd really like to hit the SQL_CACHE. Not having materialized views is bad enough. Some of my tables are quite....large. And I don't want to create a view for every user. (I'm implementing materialized views as tables in a private schema with stored procedures executing on an event trigger...but I'm looking for a way to share the schema across users)

Thanks.