Using Filesort and Temporary... Help?
Posted by:
Adam Bratt
Date: August 28, 2008 12:36AM
Alright, I've got a item price table that gets polled with the below query as many as 30 times on a PHP page load. Currently, the database is only around 50 rows but may contain as many as 25,000 in the future. For this reason, I'm hoping that I can optimize this query as much as possible.
First off here is the query. It returns all the current prices for each member group and item qty (used for bulk discounts) as well as if the item is on sale. If no price records are found for the member group it uses the price for no member group (group_id = 0).
SELECT price, qty
FROM (
SELECT p.price, p.qty
FROM store_item_prices p
WHERE p.item_id = :item_id AND
p.group_id =
IF((
SELECT COUNT(*)
FROM store_item_prices
WHERE group_id = :group_id AND
item_id = :item_id AND
IF(is_sale, (
start_date <= CURDATE() AND
end_date >= CURDATE()
), 1) AND
is_active = 1
), :group_id, 0) AND
IF(p.is_sale, (
p.start_date <= CURDATE() AND
p.end_date >= CURDATE()
), 1) AND
p.is_active = 1
ORDER BY p.is_sale DESC, p.qty ASC
) t
GROUP BY qty
Here is the table structure:
Field Type Collation Null Key Default Extra Privileges Comment
---------- ------------- --------- ------ ------ ------- -------------- ------------------------------- -------
id int(11) (NULL) NO PRI (NULL) auto_increment select,insert,update,references
item_id int(11) (NULL) NO MUL (NULL) select,insert,update,references
price decimal(12,2) (NULL) NO (NULL) select,insert,update,references
qty int(11) (NULL) NO 1 select,insert,update,references
group_id int(11) (NULL) NO 0 select,insert,update,references
is_sale tinyint(1) (NULL) NO MUL 0 select,insert,update,references
start_date date (NULL) YES (NULL) select,insert,update,references
end_date date (NULL) YES (NULL) select,insert,update,references
is_active tinyint(1) (NULL) NO 1
When I explain the query I get this:
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY <derived2> ALL NULL NULL NULL NULL 2 Using temporary; Using filesort
2 DERIVED p ALL check check 8 2 Using filesort
3 SUBQUERY store_item_prices ref check check 8 3 Using where; Using index