Mysql – 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               |
+---+--------+------------------+--------+-----------------------+---------+---+----------------------------+------+--------+----------------------------------------------+