I need help optimizing the query below. I've included the query, the table declerations, and the output of explain. Any help would be greatly appreciated.
What I'd really like is to find out how to get it to use a key on the lineitems table. I've tried adding indexes on the OrderID and MerchantID columns, but it didn't use them. SELECT Sites.TemplateName, SUM(LineItems.Quantity * LineItems.ItemPrice) as Total FROM Sites, LineItems, Orders WHERE Sites.SiteID = Orders.SiteID AND Orders.OrderStatusID>=100 AND Orders.OrderStatusID<=120 AND LineItems.OrderID=Orders.OrderID AND LineItems.MerchantID = Orders.MerchantID AND Year(Orders.OrderDate) = Year(Now()) GROUP BY Sites.TemplateName CREATE TABLE Sites ( SiteID int(10) unsigned NOT NULL auto_increment, MerchantID int(10) unsigned NOT NULL default '0', TemplateName varchar(255) NOT NULL default '', <<CLIP>> PRIMARY KEY (SiteID), UNIQUE KEY TemplateName (TemplateName) ) TYPE=MyISAM; CREATE TABLE Orders ( OrderID int(10) unsigned NOT NULL default '0', MerchantID int(10) unsigned NOT NULL default '0', SiteID int(10) unsigned NOT NULL default '0', BatchID int(10) unsigned NOT NULL default '0', CustomerID int(10) unsigned NOT NULL default '0', OrderDate datetime NOT NULL default '0000-00-00 00:00:00', OrderStatusID int(10) NOT NULL default '0', <<CLIP>> PRIMARY KEY (OrderID,MerchantID), KEY CustomerID (CustomerID), KEY MerchantID (MerchantID), KEY SiteID (SiteID) ) TYPE=MyISAM; CREATE TABLE LineItems ( LineItemID int(10) unsigned NOT NULL auto_increment, OrderID int(10) unsigned NOT NULL default '0', MerchantID int(10) unsigned NOT NULL default '0', StoreItemID int(10) unsigned NOT NULL default '0', LineItemTypeID int(10) unsigned NOT NULL default '0', ItemPrice decimal(10,2) NOT NULL default '0.00', Quantity int(10) unsigned NOT NULL default '0', Description text NOT NULL, LastModified timestamp(14) NOT NULL, PRIMARY KEY (LineItemID) ) TYPE=MyISAM; | table | type | possible_keys | key | key_len | ref | rows | Extra | | LineItems | ALL | NULL | NULL | NULL | NULL | 33675 | Using temporary | | Orders | eq_ref | PRIMARY,MerchantID,SiteID | PRIMARY | 8 | LineItems.OrderID,LineItems.MerchantID | 1 | where used | | Sites | eq_ref | PRIMARY | PRIMARY | 4 | Orders.SiteID | 1 | | --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
