How to cache select with variables? (MySQL)

Question

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.

Answer 1

For efficient fetch of a random row, here is a blog on such.

Do you really select all columns of all rows, as indicated in the timings you mention?

Keep in mind that all entires for a given table in the "Query cache" are deleted whenever any write operation happens on that table. (This makes the QC rarely of any use in busy production servers.)

A common mistake in many-to-many table is to have an AUTO_INCREMENT id. Check out that and other many:many tips here.

VIEWs are a convenience, not a way to get efficiency. Pretend you don't have the VIEW and write the real query you need. If it is 'fast enough', then I have given you the answer. If not, let's see it and look for ways to optimize it. Please provide SHOW CREATE TABLE for each relevant table.