MySQL Forums
Forum List  »  Optimizer & Parser

SQL CACHE Not used. When it should be?
Posted by: James Barwick
Date: August 10, 2010 12:41AM

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.

Options: ReplyQuote


Subject
Views
Written By
Posted
SQL CACHE Not used. When it should be?
3492
August 10, 2010 12:41AM


Sorry, you can't reply to this topic. It has been closed.
This forum is currently read only. You can not log in or make any changes. This is a temporary situation.

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.