I come across this problem every so often and end up having to do work arounds when I would prefer to keep it in the database. I have a query where the data in the query updates frequently so the query won't stay in the cache but I don't need the query to be accurate. I was trying to avoid adding an extra step of storing the data in a temp table and having to update it on a timer.
Does anyone know of a way to get MariaDB or MySQL to Keep the result set for a period of an hour or so before actually running the query again, I have an example of a query below which takes between 4.8-6 seconds to run and the wait is too long and also is needless work for the server, It can just do the query once and get the 4 numbers - then all subsequent requests just return the 4 numbers and every 20 minutes or 1 hour the 4 numbers get updated.
Is there a built in funtion for this or is the only solution to make your own table? I could end up with many of these and I don't want to end up with 100's
SELECT
SUM(CASE WHEN p.id IS NULL AND f.loc = 1 THEN 1 ELSE 0 END)/250 to_print
, SUM(CASE WHEN p.id IS NULL AND f.loc NOT IN(1,2,8) THEN 1 ELSE 0 END)/250 to_print_out
, SUM(CASE WHEN p.id IS NOT NULL AND p.print_date BETWEEN '2020-01-09 14:00:00' AND '2020-01-16 14:00:00' THEN 1 ELSE 0 END)/250 printed_tw
, SUM(CASE WHEN p.id IS NOT NULL THEN 1 ELSE 0 END)/250 printed
FROM typist t
LEFT JOIN printing p ON t.typed_id = p.print_id AND p.type = 4
LEFT JOIN files f ON t.file_id = f.file_id
WHERE
t.type = 1
AND f.file_id IS NOT NULL
AND NOT EXISTS(SELECT DISTINCT l.id FROM letters l WHERE l.deleted = 1 AND t.letter_id = l.id)
This is the EXPLAIN for the query
"1" "PRIMARY" "t" "ref" "file_id,letter_type,print_stat" "print_stat" "2" "const" "134243" "Using where; Using index"
"1" "PRIMARY" "f" "eq_ref" "PRIMARY" "PRIMARY" "3" "sys.t.file_id" "1" ""
"1" "PRIMARY" "p" "ref" "id,type" "particle_id" "8" "sys.t.typed_id" "1" "Using where"
"2" "MATERIALIZED" "l" "ref" "PRIMARY,deleted,deleted_file_id" "deleted" "1" "const" "4741" "Using index"
I have already had numerous variations to try optimising this and the above is the best I have got so far. t
has around 200,000 rows, l
has around 300,000, f
has about 130,000, p
has around 300,000 but will bloat well over 1,000,000 in time.