MYSQL query optimization

Question

I'm trying to optimize a report query run on an ecommerce site. I'm pretty sure that I'm doing something stupid, since this query shouldn't be taking nearly as long to run as it does. The query in question is:

SELECT inventories_name, inventories_code, SUM(shop_orders_inventories_qty) AS qty, 
SUM(shop_orders_inventories_price) AS tot_price, inventories_categories_name,
inventories_price_list, inventories_id 
FROM shop_orders 
LEFT JOIN shop_orders_inventories ON (shop_orders_id = join_shop_orders_id)
LEFT JOIN inventories ON (join_inventories_id = inventories_id)
WHERE {$date_type} BETWEEN '{$start_date}' AND '{$end_date}'
AND shop_orders_x_response_code = 1
GROUP BY join_inventories_id, join_shop_categories_id
{$order} 
{$limit}

It's basically trying to get total sales per item over a period of time; values in curly brackets are filled in via a form. It works fine for a period of a couple days, but querying a time interval of a week or more can take 30 seconds+.

I feel like it's joining way too many rows in order to calculate the aggregate values and sucking up huge amounts of memory, but I'm not sure how to limit it.

Note - I realize that I'm selecting fields which aren't in the group by, but they correspond 1-1 with inventory ID, which is in the group by.

Any suggestions?

-- Edit -- The current indices are:
inventories:
join_categories - BTREE
inventories_name, inventories_code, inventories_description - FULLTEXT

shop_orders_inventories:
shop_orders_inventories_id - BTREE

shop_orders:
shop_orders_id - BTREE

What indexes do these tables have?

Michael Fredrickson
– Michael Fredrickson

2011-03-22 23:00:57 +00:00
Commented Mar 22, 2011 at 23:00 — Michael Fredrickson
– Michael Fredrickson, Commented Mar 22, 2011 at 23:00

Agonych · Accepted Answer · 2011-03-22 23:07:27Z

2

Two sequential left joins will work quite long on a big table. Try to use "join" instead of "left join" (unless you have records in shop_orders with now matching records in shop_orders_inventories or inventories) or split this query to couple of small ones. Also by using "sum" and "group by" you are forcing MySQL to create temp tables - you might want to increase MySQL cache so those tables would fit in to memory (otherwise MySQL will dump them to disk which will also increase SQL execution time).

answered Mar 22, 2011 at 23:07

Agonych

3304 silver badges4 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Sam Dufel Over a year ago

Excellent advice - just switching from left join to join gave a dramatic performance increase

RichardTheKiwi · Accepted Answer · 2011-03-22 23:06:59Z

The first and foremost rule to indexing is... index the columns that you will search on!

For each possible value of {$date_type}, create an index for that date column.

Once you have lots of data in the table (say 2 years or 100 weeks), a single week's data is 1% of the index, so it becomes a good starting point.

Even though MySQL allows non-aggregates in the SELECT clause, I personally would sync the two

SELECT inventories_name, inventories_code,
       SUM(shop_orders_inventories_qty) AS qty, 
       SUM(shop_orders_inventories_price) AS tot_price,
       inventories_categories_name, inventories_price_list, inventories_id 
FROM ...
GROUP BY inventories_id, join_shop_categories_id, inventories_name,
         inventories_code, inventories_categories_name, inventories_price_list
...

Collectives™ on Stack Overflow

MYSQL query optimization

2 Answers 2

1 Comment

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Related