Cache query that is not stored in QueryCache

One of our customers has an online-shop running xt-commerce 4.2. This version of the e-commerce-software has a very expensive query for the category-tree. The query adds about 3 seconds delay and is executed on every page request. Since the software is protected by IonCube I have no possibility to change the query made by the shop software. As I found out, the query is not satisfied from the built-in MySQL-Query-Cache. The contents of the resultset do not vary very often and could be cached without problems. ###What are the possibilities to cache this one specific query? **Idea 1:** Increase RAM for MySQL so that all tables and indexes fit in RAM. - **result:** Queries duration dropped to "only" 2.5 seconds. Unsatisfactory. **Idea 2:** Find out why the query is not satisfied from the query-cache and change MySQL-Settings to cache it. - **result:** Not found settings that change the behaviour (until now). For reference: here is the query. In the slow-log it is the same query every time (no changing typos). SELECT COUNT(parent.categories_id) AS level, c.*, cd.*, su.*, cl.link_url, group_permission.*, shop.* FROM xt_categories AS c CROSS JOIN xt_categories AS parent LEFT JOIN xt_categories_description cd ON c.categories_id = cd.categories_id AND cd.categories_store_id = '1' LEFT JOIN xt_seo_url su ON (c.categories_id = su.link_id AND su.link_type = '2' AND su.store_id = '1') LEFT JOIN xt_categories_custom_link_url cl ON (cl.categories_id = c.categories_id AND cl.store_id = '1') LEFT JOIN xt_categories_permission group_permission ON (group_permission.pid = c.categories_id AND group_permission.pgroup = 'group_permission_1') LEFT JOIN xt_categories_permission shop ON (shop.pid = c.categories_id AND shop.pgroup = 'shop_1') WHERE c.categories_status = '1' AND c.categories_left BETWEEN parent.categories_left AND parent.categories_right AND cd.language_code = 'de' AND cd.categories_store_id = '1' AND ((c.category_custom_link = 0 AND su.language_code = 'de' AND su.store_id = '1') OR (c.category_custom_link = 1 AND cl.language_code = 'de' AND cl.store_id = '1')) AND group_permission.permission IS NULL AND shop.permission IS NULL GROUP BY c.categories_id , c.categories_left , c.categories_right ORDER BY c.sort_order , c.categories_left , cd.categories_name **Idea 3:** Install MySQL Proxy and implement something like https://github.com/stephan-hof/mysql-proxy-in-memory-lru-cache/blob/master/in_memory_lru_cache.lua - **result:** Works well. Changed the lua-script to only cache the specific queries. Query-times are now equal to queries satisfied from query-cache. ###Explain extended-output +---+--------+------------------+--------+-----------------------+---------+-----+---------------------------------+------+--------+----------------------------------------------+ | 1 | SIMPLE | cd | ALL | PRIMARY,language_code | | | | 5724 | 75.00 | Using where; Using temporary; Using filesort | | 1 | SIMPLE | c | eq_ref | PRIMARY | PRIMARY | 4 | dilife-eu.cd.categories_id | 1 | 100.00 | Using where | | 1 | SIMPLE | su | ref | link_id | link_id | 4 | dilife-eu.c.categories_id | 83 | 100.52 | Using where | | 1 | SIMPLE | cl | ALL | | | | | 1 | 100.00 | Using where | | 1 | SIMPLE | parent | ALL | | | | | 9530 | 100.00 | Using where; Using join buffer | | 1 | SIMPLE | group_permission | eq_ref | PRIMARY | PRIMARY | 771 | dilife-eu.c.categories_id,const | 1 | 100.00 | Using where | | 1 | SIMPLE | shop | eq_ref | PRIMARY | PRIMARY | 771 | dilife-eu.c.categories_id,const | 1 | 100.00 | Using where | +---+--------+------------------+--------+-----------------------+---------+-----+---------------------------------+------+--------+----------------------------------------------+ ###Explain output after changing all tables to MyISAM. +---+--------+------------------+--------+-----------------------+---------+---+----------------------------+------+--------+----------------------------------------------+ | 1 | SIMPLE | cl | system | PRIMARY | | | | 0 | 0.00 | const row not found | | 1 | SIMPLE | group_permission | system | PRIMARY | | | | 0 | 0.00 | const row not found | | 1 | SIMPLE | shop | system | PRIMARY | | | | 0 | 0.00 | const row not found | | 1 | SIMPLE | cd | ALL | PRIMARY,language_code | | | | 5724 | 75.00 | Using where; Using temporary; Using filesort | | 1 | SIMPLE | c | eq_ref | PRIMARY | PRIMARY | 4 | dilife-eu.cd.categories_id | 1 | 100.00 | Using where | | 1 | SIMPLE | su | ref | link_id | link_id | 4 | dilife-eu.c.categories_id | 83 | 100.57 | Using where | | 1 | SIMPLE | parent | ALL | | | | | 9791 | 100.00 | Using where; Using join buffer | +---+--------+------------------+--------+-----------------------+---------+---+----------------------------+------+--------+----------------------------------------------+