Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
Add the SHOW CREATE TABLE outputs of the tables. Do you have composite indexes on the orders_products table? –  ypercube May 14 '13 at 20:03
    
@ypercube updated –  ben lemasurier May 14 '13 at 20:13
    
can you update us on what you changed and the new execution plan? We don't get many questions for MariaDB and I'm curious for the plan. –  ypercube May 15 '13 at 22:11

3 Answers 3

up vote 5 down vote accepted

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.

share|improve this answer

I Don't know your database but hope maybe this will help

I setup some test data so I have an orders_products table with 27,000 rows populated like this so I have each order with three random items from a possible set of 10

Create Table orders_products (Orders_Id Number, Products_Id Number);
create index orders_products_I1 on orders_products(orders_id);
create index orders_products_I2 on orders_products(products_id);

I've assumed you don't have composite keys like Oracle so just created two indexes each with a single id.

Declare 
 P_Id Number(3);
 o_id Number(4);
Begin
  For o_id In 1000..9999
  Loop
     P_Id:=trunc(dbms_random.value(1,9))*100;
     insert into orders_Products values (o_id, p_id);
     P_Id:=trunc(dbms_random.value(1,9))*100;
     Insert Into Orders_Products Values (O_Id, P_Id);
     P_Id:=trunc(dbms_random.value(1,9))*100;
     Insert Into Orders_Products Values (O_Id, P_Id);
  End Loop;
  Commit;
END;

This is how I would rewrite your query in Oracle which just gets a list of items and counts

select p.* from products_id
 where products_id in ( 
     select products_id
       From Orders_Products Op
      where op.orders_id in (select orders_id 
                               from orders_products opf
                              Where Exists (Select 1 
                                              from orders_products ope
                                             Where Products_Id=100)
                            ) 
         And Op.Products_Id <> 100
       Group By Products_Id
       order by count(*) desc)
   and rownum <=5

This runs in a fraction of a second (Elapsed: 00:00:00.234) and would be faster each time in Oracle if using bind variables instead of hardcoding the products_id so if you have bind variables in your database, that might help. If you have exists, it only has to find the first occurrence and not all occurrences that might match.

Only bit I omitted was to select all the products details for the items - could put a wrapper around the outside of this query to get that so that all product details are only returned for the 5 products you want and not for all rows which are then limited based on the sort.

This is the result of the inner query with the total included

PRODUCTS_ID TOTAL
----------- -----
    700  3486 
    600  3406 
    400  3393 
    300  3389 
    200  3322 
    800  3320 
    500  3309 

Elapsed: 00:00:00.234

and this is the result excluding it so we can use the products_id in the outer select

PRODUCTS_ID

    700 
    600 
    400 
    300 
    200 
    800 
    500 

Elapsed: 00:00:00.405

share|improve this answer

The first operation looks expensive, with the warning signs Using temporary; Using filesort. Consider adding an index on orders_products that allows you to search on products_id:

create index IX_OrdersProcucts_ProductId
    on orders_products(products_id, orders_id);

By adding orders_id in addition to products_id, the index becomes covering for:

SELECT orders_id
FROM orders_products
WHERE products_id = 100

If you add this index, the first step in the explain extended output should include Using index.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.