Cache rebuilding of TEMPTABLE views

[Balazs Jantek]

hi! I have a query that uses many string functions for logging in users. it is too slow and we need to improve its performance. unfortunately the table structure cannot be changed, but I thought of making it faster by creating a view:

select u.uid, u.name, u.pass from `users` u, `profile_values` pv where username=? and pass=? and (
datediff(str_to_date(concat_ws('-', mid(pv.value, 22,4), trim(both '"' from mid(pv.value, 44,3)), trim(both '"' from trim(both ';' from trim(both '"' from mid(pv.value, 63,3))))), '%Y-%c-%e'),curdate()) > -3 and u.uid = pv.uid and pv.fid = 4)

my question is: if I create a view for it and set the view algorithm to TEMPTABLE (to cache the string+date manipulation results) do I have to care about the cache invalidation, that occurs when data changes in the underlying tables?
Will MySQL invalidate the temporary table's content and recalculate it automatically?

The documentation at this point wasn't clear enough for me.. Anyways, do you think using this TEMPTABLE view will give me real performance gain, or simply this repeating query will keep the results in memory without the view?

best regards, jb



Edited 1 time(s). Last edit at 02/07/2011 02:40AM by Balazs Jantek.