Optimize MariaDB - avoid "Copying to tmp table" - find joins without index

Question

I have a website which is backed by a MariaDB database. The DB seems to be the bottleneck to the sites performance. I spend quite some time on optimizing various DB parameters, but now I am stuck with a lot of "Copying to tmp table" operations and joins without index.

Any idea on how to avoid them? Or how to optimize the DB?

The DB uses only InnoDB tables.

---

If I use SHOW FULL PROCESSLIST to look at the current queries, there seem to be no slow queries. All queries show a time of 0-1 seconds. I had this on auto refresh every second, and not a single query reached 2 seconds. I also tried manually running some of the "slow" 1 second queries and they all finish within 1 - 1.5 seconds.

While looking at SHOW FULL PROCESSLIST I can see that many queries are in the Copying to tmp table state. I deduct from that, that this process step is probably the slowest. What causes it? How can I avoid it?

The DB is ~8 GB and everything should fit into RAM.

The queries also produce small results (e.g., 5 rows). Using explain also only shows a maximum of <100 rows.

---

I optimized the DB settings with mysqltuner.pl. Everything got faster, but I am still stuck with a lot of joins without index (according to mysqltuner). I don't know how to find them. I tried looking for frequent queries with SHOW FULL PROCESSLIST and took a look at them. All joins use indexed columns.

---

Some othe hints from mysqltuner where I don't know how to adress them:

...
[OK] Slow queries: 0% (0/3M)
[OK] Highest usage of available connections: 0% (34/15954)
[OK] Aborted connections: 0.00% (0/34999)
[!!] Query cache may be disabled by default due to mutex contention.
[OK] Query cache efficiency: 41.4% (2M cached / 5M selects)
[!!] Query cache prunes per day: 21506680
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 321K sorts)
[!!] Joins performed without indexes: 127051
[OK] Temporary tables created on disk: 21% (81K on disk / 373K total)
[OK] Thread cache hit rate: 91% (2K created / 34K connections)
[!!] Table cache hit rate: 0% (200 hits / 23K requests)
[OK] table_definition_cache (1024) is greater than number of tables (629)
[OK] Open file limit used: 0% (5/16K)
[OK] Table locks acquired immediately: 100% (5M immediate / 5M locks)
...
-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[--] InnoDB Thread Concurrency: 0
[OK] InnoDB File per table is activated
[OK] InnoDB buffer pool / data size: 12.0G / 8.1G
[OK] Ratio InnoDB log file size / InnoDB Buffer pool size: 1.5G * 2/12.0G should be equal to 25%
[OK] InnoDB buffer pool instances: 12
[--] InnoDB Buffer Pool Chunk Size not used or defined in your version
[OK] InnoDB Read buffer efficiency: 99.99% (3911916061 hits / 3912183382 total)
[!!] InnoDB Write Log efficiency: 61.91% (60693 hits / 98030 total)
[OK] InnoDB log waits: 0.00% (0 waits / 37337 writes)

---

Here is an example query. The query is generated. I know that the query itself could be better, but I can not change the software.

SELECT
    product.id as __product_id,
    variant.id as __variant_id,
    variant.ordernumber as __variant_ordernumber
FROM
    s_articles product
INNER JOIN s_articles_details variant ON
    variant.id = product.main_detail_id
    AND variant.active = 1
    AND product.active = 1
INNER JOIN s_articles_categories_ro productCategory ON
    productCategory.articleID = product.id
    AND productCategory.categoryID IN (3156292)
LEFT JOIN s_articles_avoid_customergroups avoidCustomerGroup ON
    avoidCustomerGroup.articleID = product.id
    AND avoidCustomerGroup.customerGroupId IN (4)
INNER JOIN s_articles_categories similarMain ON
    similarMain.articleID = product.id
    AND similarMain.articleID != '435'
INNER JOIN s_articles_categories similarSub ON
    similarSub.categoryID = similarMain.categoryID
    AND similarMain.articleID != similarSub.articleID
    AND similarSub.articleID = '435'
LEFT JOIN s_articles_top_seller_ro topSeller ON
    topSeller.article_id = product.id
INNER JOIN s_articles_attributes productAttribute ON
    productAttribute.articledetailsID = variant.id
WHERE
    avoidCustomerGroup.articleID IS NULL
GROUP BY
    product.id
ORDER BY
    topSeller.sales ASC,
    variant.id ASC
LIMIT 5

this is the explain for that

id|select_type|table             |type  |possible_keys                                                                         |key             |key_len|ref                              |rows|Extra                                       |
--+-----------+------------------+------+--------------------------------------------------------------------------------------+----------------+-------+---------------------------------+----+--------------------------------------------+
 1|SIMPLE     |similarSub        |ref   |articleID,categoryID,articleID_2                                                      |articleID       |4      |const                            |  12|Using index; Using temporary; Using filesort|
 1|SIMPLE     |similarMain       |ref   |articleID,categoryID,articleID_2                                                      |categoryID      |4      |devdb.similarSub.categoryID      |  62|Using where                                 |
 1|SIMPLE     |product           |eq_ref|PRIMARY,main_detailID,product_newcomer,get_category_filters,s_articles_active_IDX     |PRIMARY         |4      |devdb.similarMain.articleID      |   1|Using where                                 |
 1|SIMPLE     |topSeller         |eq_ref|article_id                                                                            |article_id      |4      |devdb.similarMain.articleID      |   1|                                            |
 1|SIMPLE     |productCategory   |ref   |articleID,categoryID,articleID_2,categoryID_2,category_id_by_article_id,elastic_search|articleID       |8      |devdb.similarMain.articleID,const|   1|Using index                                 |
 1|SIMPLE     |productAttribute  |ref   |articledetailsID                                                                      |articledetailsID|5      |devdb.product.main_detail_id     |   1|Using index                                 |
 1|SIMPLE     |variant           |eq_ref|PRIMARY,s_articles_details_id_IDX                                                     |PRIMARY         |4      |devdb.product.main_detail_id     |   1|Using where                                 |
 1|SIMPLE     |avoidCustomerGroup|eq_ref|PRIMARY,articleID                                                                     |PRIMARY         |8      |devdb.similarMain.articleID,const|   1|Using where; Using index; Not exists        |

Answer 1

Using index says that the Optimizer could use the index, by itself, to reach into the table. This is usually optimal. (It may or may not be a "composite" index.)

These indexes may be optimal:

product:  INDEX(main_detail_id, id,  active)
productCategory:  INDEX(articleID,  categoryID)
avoidCustomerGroup:  INDEX(articleID,  customerGroupId)
similarMain:  INDEX(articleID,  categoryID)
similarSub:  INDEX(categoryID,  articleID)
topSeller:  INDEX(article_id,  sales)
productAttribute:  INDEX(articledetailsID)

(Without seeing SHOW CREATE TABLE, I cannot tell if you already have such indexes. Nor can I advise further on whether the "x,y" discussed below applies.)

IN (3156292) is the same as = 3156292. But IN (22, 33) is harder to optimize.

The unrelated GROUP BY and ORDER BY lead to two temp tables and two sorts of them.

INNER JOIN ...
        ON  variant.id = product.main_detail_id  -- relation
       AND  variant.active = 1    -- filter
       AND  product.active = 1    -- filter

Please move the "filtering" to WHERE; this could help me (and others) in analyzing how to speed up the query. Caveat: LEFT JOIN may need the filtering in the ON clause.

One performance technique is to turn the query inside out to get finished with the LIMIT 5 before all the JOINs. Something like

SELECT ...
    FROM ( SELECT ... -- as little as possible
            FROM product
            JOIN variant ON ...
            LEFT JOIN topseller ON ...
            WHERE ...
            GROUP BY ...
            ORDER BY ...
            LIMIT ...
         ) AS x
     JOIN ...  -- the other tables
     JOIN ...  -- and possibly the original tables (to get more cols)
     ORDER BY ...   -- repeated
     -- (no LIMIT needed)

(With the relationships and filters mixed together, I don't want to attempt to provide details on this possible optimization.)

What I see in that partial tuner output:

• Turn off the Query Cache
• The buffer_pool is a good size (until the dataset grows a lot more)
• Set long_query_time = 1; collect for a day; then use pt-query-digest to see which queries are the naughtiest. This won't lead to all the "joins without indexes", but it will lead to the important ones.

Re your Comment about 'composite' indexes:

• Yes, for the given example, INDEX(x,y) (in either order) is better than INDEX(x), INDEX(y).
• If you have INDEX(x,y), you do not need INDEX(x), but you may still need some index starting with y.
• More: Index Cookbook