1

I've got a large (~30 million records) table full of sale information. To supplement, I've also got a smaller table containing the item information.

Here's the item information schema:

CREATE TABLE tbl_items (
    id int(11) not null auto_increment,
    category_id int(11) not null auto_increment,
    name varchar(40) not null,
    price decimal(11, 2) not null,
    primary key(`id`),
    key `item_key1` (`category_id`),
    foreign key (`category_id`) references `tbl_categories` (`id`)
);

and here is the sales table:

CREATE TABLE tbl_sales (
    id int(11) not null auto_increment,
    item_id int(11) not null,
    sale_date date not null,
    sale_time time not null,
    qty decimal (11, 2) not null,
    primary key (`id`),
    key `sales_key1` (`sale_date`, `sale_time`),
    key `fk_sale_item` (`item_id`),
    foreign key (`item_id`) references `tbl_items` (`id`)
);

I've got a query that calculates the total sale amount for each item, in a particular category, within a particular timeframe:

SELECT tbl_items.name AS `Item Name`,
       sum(tbl_items.price * tbl_sales.qty) AS `Amount` 
FROM tbl_sales
left outer join tbl_items ON tbl_sales.item_id = tbl_items.id 
WHERE tbl_items.category_id = 3
AND tbl_sales.sale_date >= '2016-08-22' and tbl_sales.sale_date < '2016-08-29'
GROUP BY `Item Name` 
ORDER BY `Amount` DESC;

Here's the query plan that it comes up with:

+------+-------------+------------+------+--------------------------+---------------+---------+---------------------+------+-----------------------------------------------------------+
| id   | select_type | table      | type | possible_keys            | key           | key_len | ref                 | rows | Extra                                                     |
+------+-------------+------------+------+--------------------------+---------------+---------+---------------------+------+-----------------------------------------------------------+
|    1 | SIMPLE      | tbl_items  | ref  | PRIMARY,item_key1        | item_key1     | 5       | const               |  322 | Using where; Using index; Using temporary; Using filesort |
|    1 | SIMPLE      | tbl_sales  | ref  | sales_key1,fk_sale_item  | fk_sale_item  | 5       | pxtest.tbl_items.id |  629 | Using where                                               |
+------+-------------+------------+------+--------------------------+---------------+---------+---------------------+------+-----------------------------------------------------------+

Unfortunately it's using the wrong join order and the wrong index. The plan it should be using is this:

Interestingly, this plan is used when the date range is shrunk down (the data pretty much finishes on the 23rd)

SELECT tbl_items.name AS `Item Name`,
       sum(tbl_items.price * tbl_sales.qty) AS `Amount`
FROM tbl_sales
left outer join tbl_items ON tbl_sales.item_id = tbl_items.id
WHERE tbl_items.category_id = 3
AND tbl_sales.sale_date >= '2016-08-23' and tbl_sales.sale_date < '2016-08-29'
GROUP BY `Item Name`
ORDER BY `Amount` DESC;

,

+------+-------------+-----------+--------+-------------------------+------------+---------+--------------------------+------+---------------------------------------------------------------------+
| id   | select_type | table     | type   | possible_keys           | key        | key_len | ref                      | rows | Extra                                                               |
+------+-------------+-----------+--------+-------------------------+------------+---------+--------------------------+------+---------------------------------------------------------------------+
|    1 | SIMPLE      | tbl_sales | range  | sales_key1,fk_sale_item | sales_key1 | 4       | NULL                     |   11 | Using index condition; Using where; Using temporary; Using filesort |
|    1 | SIMPLE      | tbl_items | eq_ref | PRIMARY,item_key1       | PRIMARY    | 4       | pxtest.tbl_sales.item_id |    1 | Using where                                                         |
+------+-------------+-----------+--------+-------------------------+------------+---------+--------------------------+------+---------------------------------------------------------------------+

This plan is the correct plan, and it should be used for the former query. For reference, here are some counts on the dataset:

select count(1) from tbl_sales where item_id in
      (select id from tbl_items where category_id = 3);
# 10,327,729

select count(1) from tbl_salesitems;
# 29,995,348

select count(1) from tbl_salesitems where itemdate >= '2016-08-22';
# 737,543

select count(1) from tbl_salesitems where itemdate >= '2016-08-23';
# 11

I know I can force an index to be used, but that's not what I'm after. What I want to know is why it chooses that plan, when it is clearly better to restrict the dataset to the date range first?

4
  • 1
    Two columns with auto_increment??? – Rick James Jan 27 '17 at 6:07
  • You never change the price of an item? – Rick James Jan 27 '17 at 6:10
  • sorry that was a typo. And yes, the price of an item can change, but it is inserted as a new record to preserve historic data. – Alec Jan 30 '17 at 23:41
  • I usually recommend 2 tables -- one with current prices, one with the history. They would have a similar set of columns; the history would (eventually) have a lot more rows. Some of the issues here would vanish, performance would improve, and queries would be simpler. (Usually.) – Rick James Jan 31 '17 at 1:50
2

It is not clear that starting with the date range is faster. Did you do FORCE INDEX and discover that it was faster?

The "reason" for the different query plans is that the "statistics" imply that one would probably work better when than the other, based on the estimated number of rows to fetch. Alas, when the WHERE clause hits two joined tables, the statistics can't really do a good job.

Anyway, as you found out, FORCE is dangerous because it can help one variant, but hurt another.

It is unclear whether either of the queries avoids needing two tmp tables and filesorts -- one for GROUP BY and another for ORDER BY. Change to tbl_items.name so the Optimizer won't have to deduce that for itself (assuming it actually does). Then change the category index to

INDEX(category_id, name)

in hopes that it will avoid the sort for GROUP BY.

For the other direction, this would speed things up some because it is "covering":

INDEX(sale_date, index_id, qty)

If your version has it, please provide EXPLAIN FORMAT=JSON SELECT ... -- that might answer some of the things I am guessing at.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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