Mysql – How to improve this query

mariadbMySQLperformancequery-performance

About a year back I introduced a query which returns a sort of "Customers Also Purchased" data-set. At the time it ran reasonably fast however, as of late it's become very slow, sometimes taking up to 5 seconds or more.

SELECT p.*, COUNT(*) AS total
FROM orders_products AS op
JOIN products AS p ON p.products_id = op.products_id
JOIN (
  SELECT orders_id
  FROM orders_products
  WHERE products_id = 100
) AS opf ON opf.orders_id = op.orders_id
WHERE op.products_id <> 100
GROUP BY products_id
ORDER BY total DESC
LIMIT 5;

Output from explain extended:

+------+-------------+-----------------+--------+-----------------------+-------------+---------+------------------------------------+------+----------+---------------------------------+
| id   | select_type | table           | type   | possible_keys         | key         | key_len | ref                                | rows | filtered | Extra                           |
+------+-------------+-----------------+--------+-----------------------+-------------+---------+------------------------------------+------+----------+---------------------------------+
|    1 | SIMPLE      | orders_products | ref    | products_id,orders_id | products_id | 4       | const                              | 4511 |   100.00 | Using temporary; Using filesort |
|    1 | SIMPLE      | op              | ref    | products_id,orders_id | orders_id   | 4       | database.orders_products.orders_id |    2 |   100.00 | Using where                     |
|    1 | SIMPLE      | p               | eq_ref | PRIMARY               | PRIMARY     | 4       | database.op.products_id            |    1 |   100.00 |                                 |
+------+-------------+-----------------+--------+-----------------------+-------------+---------+--------------------------------------+------+----------+-------------------------------+

SHOW CREATE TABLE products:

+----------+----------------------------------------------------------------+
| Table    | Create Table                                                   
+----------+----------------------------------------------------------------+
| products | CREATE TABLE `products` (
  `products_id` int(11) NOT NULL AUTO_INCREMENT,
  `products_model` varchar(128) DEFAULT NULL,
  `products_price` decimal(15,4) NOT NULL DEFAULT '0.0000',
  `products_date_added` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `products_last_modified` datetime DEFAULT NULL,
  `products_status` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`products_id`),
  KEY `idx_products_date_added` (`products_date_added`),
  KEY `products_model` (`products_model`),
  KEY `products_price` (`products_price`),
  KEY `products_status` (`products_status`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+----------+----------------------------------------------------------------+

SHOW INDEXES FROM products

+----------+------------+----------------------------------+--------------+----------------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table    | Non_unique | Key_name                         | Seq_in_index | Column_name                | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------+------------+----------------------------------+--------------+----------------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| products |          0 | PRIMARY                          |            1 | products_id                | A         |        4356 |     NULL | NULL   |      | BTREE      |         |               |
| products |          1 | idx_products_date_added          |            1 | products_date_added        | A         |        4356 |     NULL | NULL   |      | BTREE      |         |               |
| products |          1 | products_model                   |            1 | products_model             | A         |        4356 |     NULL | NULL   | YES  | BTREE      |         |               |
| products |          1 | products_price                   |            1 | products_price             | A         |        1089 |     NULL | NULL   |      | BTREE      |         |               |
| products |          1 | products_status                  |            1 | products_status            | A         |           4 |     NULL | NULL   |      | BTREE      |         |               |
+----------+------------+----------------------------------+--------------+----------------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

SHOW CREATE TABLE orders_products:

+-----------------+--------------------------------------------------------+
| Table           | Create Table                                           
+-----------------+--------------------------------------------------------+
| orders_products | CREATE TABLE `orders_products` (
  `orders_products_id` int(11) NOT NULL AUTO_INCREMENT,
  `orders_id` int(11) NOT NULL DEFAULT '0',
  `products_id` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`orders_products_id`),
  KEY `products_id` (`products_id`),
  KEY `orders_id` (`orders_id`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-----------------+--------------------------------------------------------+

SHOW INDEXES FROM orders_products

+-----------------+------------+-------------+--------------+--------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table           | Non_unique | Key_name    | Seq_in_index | Column_name        | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-----------------+------------+-------------+--------------+--------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| orders_products |          0 | PRIMARY     |            1 | orders_products_id | A         |     3134198 |     NULL | NULL   |      | BTREE      |         |               |
| orders_products |          1 | products_id |            1 | products_id        | A         |        5014 |     NULL | NULL   |      | BTREE      |         |               |
| orders_products |          1 | orders_id   |            1 | orders_id          | A         |     1567099 |     NULL | NULL   |      | BTREE      |         |               |
+-----------------+------------+-------------+--------------+--------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

Is there something obvious I'm missing here? In most regards the database appears to well tuned. We're running MariaDB 5.5.30 under the InnoDB/XtraDB storage engine.

Best Answer

This (orders_products) is a many-to-many table. I think it's common to have 2 composite indexes on such tables as it helps in many common queries.

I would definitely add two (unique) indexes, on (orders_id, products_id) and on (products_id, orders_id).

Not sure if defining them both as UNIQUE would be a further improvement in MariaDB's optimizer.

And if there is not some special reason, you could drop the auto incremented orders_products_id column. You can identify rows in the table by the order and product ids. I think that column only adds more space in the table and indexes and no value.