I have this use case: I need to execute multiple times the same "logic" query, a fixed number of times, over the same table (same semantic, varying only the values with which the "WHERE" statements are compared).
Query layout:
SELECT [(SUM(col_name),col_name,...)]
FROM table_name
WHERE expr AND expr...
I pretend to improve performance for this task.
From reading articles I've found here on this issue, and some extra research, I can point out the following relevant facts:
If I create a temporary table in memory (RAM, ENGINE=MEMORY) mirroring the table in question, and then execute all the queries over this in-memory table, can I improve performance?:
CREATE TABLE tmp_table_name ENGINE=MEMORY SELECT * FROM table_name;Perform the queries over tmp_table_name
DROP TABLE tmp_table_name;
see MySQL docs: The MEMORY (HEAP) Storage Engine
Thanks.
Will MEMORY improve performance? Mostly no.
No, you can't use MEMORY if you you can't use MEMORY - see limitations
No, MEMORY is slower if you spend too much time creating the table.
No because the copy won't be up to date. (OK, maybe that is not an issue.)
No because you are stealing from other caches, making other queries slower.
No -- If VARCHARs turn into CHARs in your version, the MEMORY table could be so much bigger than the non-MEMORY table that other issues come into play.
Maybe -- If everything is cached in RAM anyway, MEMORY will run at about the same speed.
Your particular example can possibly be sped up by