I currently have 2 tables in my MySQL server:
What I want to do is first get data from the 'patent' table and store that in a cache. Then, using the cache, I want to use that in an SQL query to call another, more specific data from the 'inventor' table.
The patent SQL query is:
SELECT application_number, application_date, research_center FROM patent WHERE research_center = 'MIT';
The 'inventor' table also has an 'application_number' column and I can just use the INNER JOIN to get the needed data from the 'inventor' table.
However, I was wondering if it is possible to cache the 'patent' query data and use that as a table to INNER JOIN the 'inventor' table?
eg:
SELECT t1.inventor, t1.application_number FROM (SELECT inventor.* FROM cache LEFT JOIN inventor ON cache.application_number = inventor.application_number) AS t1.
where 'cache' is the cached table.
Is this possible?
Thank you!
Your SQL query runs against your DB while your cached data is in another place (in memory, Redis, ...). So, you cannot use that as a table to INNER JOIN the 'inventor' table.
As a thought experiment, you could cache the result from your first query (look into how to use @Cacheable in a service method) and then use those values to fill the params in your second query in the model layer.
But since your tables are usually in the same DB, it does not make sense to do this. Your DBMS would do it faster for you.