Very slow mysql query for reading orders (joins and group by)

Question

A query for reading orders is very slow. I've tried a lot of things, but couldn't get a faster query. Below the query and the database tables. Of course, all the important fields have an index. I noticed that removing the group by function will speed it up, but grouping the query is important.

is there a better way for getting the orders? Thanks in advance.

SELECT
orders.id AS orderId, orders.delivery_from, orders.delivery_to,
orders_products.product_id, orders_products.color_id, ,orders_products.size_id,
sum(orders_products.quantity) as quantity,
customers.id AS customerId, customers.name AS customerName,
products.name
FROM orders
    INNER JOIN orders_products ON orders_products.order_id=orders.id
    INNER JOIN customers ON customers.id=orders.customer_id
    INNER JOIN products ON orders_products.product_id=products.id
    LEFT JOIN orders_product_data ON orders_product_data.order_id=orders.id AND orders_product_data.product_id=orders_product.product_id AND orders_product_data.color_id=orders_product.color_id
WHERE orders.status='0' AND
(orders.delivery_from<='2014-05-05' AND orders.delivery_to>='2014-05-05') AND
((orders_products_data.delivery_from<='2014-05-05' || orders_products_data.delivery_to=0) AND (orders_products_data.delivery_from>='2014-05-05' || orders_products_data.delivery_to=0))
GROUP BY customer_id, product_id, color_id, size_id

Customers

CREATE TABLE IF NOT EXISTS `customers` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(90) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)
);

Products

CREATE TABLE IF NOT EXISTS `products` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)
);

Orders

CREATE TABLE IF NOT EXISTS `orders` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `customer_id` int(11) NOT NULL,
  `delivery_from` date NOT NULL,
  `delivery_to` date NOT NULL,
  `status` int(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
);

Products of the order

CREATE TABLE IF NOT EXISTS `orders_products` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `order_id` int(11) NOT NULL DEFAULT '0',
  `product_id` int(11) NOT NULL DEFAULT '0',
  `color_id` int(11) NOT NULL DEFAULT '0',
  `size_id` int(11) NOT NULL DEFAULT '0',
  `quantity` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
);

Color data. The tabel below stores for the combination order_id, product_id and color_id the delivery information since the delivery of products in different colors could change.

CREATE TABLE IF NOT EXISTS `orders_products_data` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `order_id` int(11) NOT NULL DEFAULT '0',
  `product_id` int(11) NOT NULL DEFAULT '0',
  `color_id` int(11) NOT NULL DEFAULT '0',
  `delivery_from` date NOT NULL,
  `delivery_to` date NOT NULL,
  PRIMARY KEY (`id`)
);

The explain query enter image description here

simple rule of thumb: any field used in a `where`, `join`, `group`, or `order` clause should have an index on it. right now it looks like your only indexes are the primary key on the `id` fields. doing an `explain ...` on the entire query will tell you what's happening in the background as well. — Marc B, May 05 '14 at 15:10
simple rule of thumb: without a result of the same query perpended with word EXPLAIN, no such question makes any sense. while just adding indexes blindly can make things worse — Your Common Sense, May 05 '14 at 15:14
I've made an index on all the fields, but didn't post it below the tables (I've just mentioned it in the question). — Stefan, May 05 '14 at 15:16
How long does this query take to run? How many rows is it returning? — Noah, May 05 '14 at 16:06
Please show us the indexes that you've created. Perhaps you've created them incorrectly. — Andy Lester, May 05 '14 at 16:10
It takes about 0.27 sec for 282 results. But there are at the moment just 638 products. There will be thousands of rows. I've created indexes on all the ints. The explain is in the question above. — Stefan, May 05 '14 at 16:23
I've added a composite index for the table orders_products_data and the fields order_id, product_id and color_id. But since this table haven't yet just a few rows, it wouldn't fasten the query. — Stefan, May 06 '14 at 06:30
@AndyLester what would you know about the indexes? I've used BTREE indexes without a unique value and A Collation. Cardinality looks right. — Stefan, May 06 '14 at 07:40

score 1 · Accepted Answer · answered May 06 '14 at 11:42

1

You have mentioned that you have indexes for all the important fields.

Just for your Info - Index should be prepared based on the access path rather than for important fields.

Check after replacing delivery_from between '...' and '...' for orders, orders_product_data and adding following index: create index idx_orders_status_delivery_from_to on orders(status,delivery_from,delivery_to);

answered May 06 '14 at 11:42

Suresh Gautam

816
8
21

Thnx for your answer serush. I've made an extra index for that and it is faster right now. Do you have more tips for indexing? As commented before, I've apse made an index for orders_products_data for the fields order_id, product_id and color_id. – Stefan May 06 '14 at 12:03
Is there an possibility for counting the quantity of products for each individual customer without using almost the same query as an inner join? – Stefan May 06 '14 at 16:45
@Stefan - Thanks for accepting the answer. Following links will clearify about indexing and how it workds: http://stackoverflow.com/questions/1108/how-does-database-indexing-work http://www.programmerinterview.com/index.php/database-sql/what-is-an-index/ I believe that you will be able to figure out required index if understand how index works and overhead we get in db after adding index on the table. – Suresh Gautam May 08 '14 at 06:17

Very slow mysql query for reading orders (joins and group by)

1 Answers1