Day: January 12, 2020

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.