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

Solution 1: After harmonizing all tables to MyISAM the Query-Cache of MySQL worked as expected again. Added some indexes for cd and parent to speedup the queries about 30%.

Solution 2: Set up MySQL-Proxy and created LUA-Script to cache result of query in MySQL-Proxy. Shop-Software was set to use the MySQL-Proxy on Port 3307 as MySQL-Server.

apt-get install mysql-proxy
nano /etc/default/mysql-proxy

My config for MySQL-Proxy nano /etc/default/mysql-proxy:

ENABLED="true"
OPTIONS="--proxy-lua-script=/usr/share/mysql-proxy/in-memory-lru-cache.lua
     --proxy-address=:3307
     --proxy-backend-addresses=127.0.0.1:3306
     --log-file=/var/log/mysql-proxy.log
     --admin-username=xxxxxxxxxx
     --admin-password=xxxxxxxxxx
     --admin-lua-script=/usr/lib/mysql-proxy/lua/admin.lua"

Changes to /usr/share/mysql-proxy/in-memory-lru-cache.lua:

Line 104: if query.find(query,',c.%*,cd.%*,su.%*,cl.link_url,group_permission.%*,shop.%*') ~= nil then

The original file was from https://github.com/stephan-hof/mysql-proxy-in-memory-lru-cache/blob/master/in_memory_lru_cache.lua

StackExchange Database Administrators Q#138755, answer score: 3