MySQL Forums
Forum List  »  Optimizer & Parser

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

Options: ReplyQuote


Subject
Views
Written By
Posted
Using Filesort and Temporary... Help?
3566
August 28, 2008 12:36AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.