How to cache select with variables? (MySQL)

## Context: I have complicated very often executed query: CREATE OR REPLACE VIEW ordered_words AS SELECT word.id FROM category JOIN frequency ON category.id = frequency.category_id JOIN word ON frequency.word_id = word.id JOIN language ON word.language_id = language.id WHERE category.name='Subtitles' AND iso_639_1='en' ORDER BY frequency.value DESC; I enabled cache query_cache_type=1 and I obtain great performance improvement. **First time (4.5 s)** sql> SELECT * FROM ordered_words 500 rows retrieved starting from 1 in 4s 585ms (execution: 4s 576ms, fetching: 9ms) **Second time (5 ms)** sql> SELECT * FROM ordered_words 500 rows retrieved starting from 1 in 11ms (execution: 5ms, fetching: 6ms) It is ok. ------------------- ## Problem But now I want to add to this result one column with identifiers 1,2,3... because I would like to be able to get selected row from result of this query. I created the following query: SELECT @ROW := @ROW + 1 AS ROW FROM ordered_words t join (SELECT @ROW := 0) t2; But this query is not cached. Any time execution gets about 4.8 sec. I want to save to cache result of this query. ### Alternative solutions: Because of my experience in databases is small. I do not know if my approach is optimal so I describe wider context. In my application inserts are very rarely used. My real target is execute very often query that get one random element from ordered_words with dynamically assigned distribution of probability. I do not want to create new table for this, because category and language are free parameters. I will use function rather. But I do not want execute all query any time. Logic of randomization will be executed out of MySQL. MySQL will obtain only number of word in temporal order saved in result of ordered_words. I am planning treat column row as temporary identifier that allow select word_id without calculating all joins and ordering from first mentioned query. Maybe is there something like temporary table created in memory, or should I use other database system?  ---------------- # Update As @Rick-James suggest. Timing was wrong. Cache was created by my IDE - DataGrip not by MySQL. Finally I decided to save result of my complicated query to table in memory by command: CREATE TABLE IF NOT EXISTS words_memory_subtitles_en (id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, word_id INTEGER) ENGINE=MEMORY AS SELECT word.id as word_id FROM category JOIN frequency ON category.id = frequency.category_id JOIN word ON frequency.word_id = word.id JOIN language ON word.language_id = language.id WHERE category.name='Subtitles' AND iso_639_1='en' ORDER BY frequency.value DESC; I will mange creating and removing tables like this in PHP. I decided to remove id from frequency table.