Hi All; I inherited an old Access database at work which was not at all normalized. I then managed to normalize the schema, but I am running into problems. First let me show you the two tables I need to query:
------------------------------------------------------------ -- # Host: 10.1.1.14 # Database: pricelink3 # Table: 'Item' # CREATE TABLE `Item` ( `Itemcode` char(16) NOT NULL default 'INVALID', `Supplier_ID` int(10) unsigned NOT NULL default '0', `Pricefile_ID` int(10) unsigned NOT NULL default '0', `Itemcode_Stripped` char(16) NOT NULL default '', `RowNum` int(10) unsigned NOT NULL auto_increment, `LastChanged` timestamp(14) NOT NULL, `Description` char(24) NOT NULL default 'No Description Available', `ListPrice` decimal(10,2) NOT NULL default '0.00', `Price1` decimal(10,2) NOT NULL default '0.00', `Price2` decimal(10,2) NOT NULL default '0.00', `Price3` decimal(10,2) NOT NULL default '0.00', `Price4` decimal(10,2) NOT NULL default '0.00', `Price5` decimal(10,2) NOT NULL default '0.00', `Cost` decimal(10,2) NOT NULL default '0.00', `CoreCost` decimal(10,2) NOT NULL default '0.00', `CoreSelling` decimal(10,2) NOT NULL default '0.00', `Weight` decimal(10,2) NOT NULL default '0.00', `Package` smallint(5) unsigned default NULL, `PriceEffectiveDate` date default NULL, `Barcode` char(40) default NULL, `URL` char(255) default NULL, `Popularity` char(2) default NULL, `File_ID` mediumint(8) unsigned default NULL, PRIMARY KEY (`Supplier_ID`,`Itemcode`), UNIQUE KEY `RowNum` (`RowNum`), KEY `Pricefile_ID` (`Pricefile_ID`), KEY `UpdateIndex` (`Itemcode`), KEY `FileRef` (`File_ID`), KEY `lastchang` (`LastChanged`), FOREIGN KEY (`Supplier_ID`) REFERENCES `Supplier` (`Supplier_ID`) ON UPDATE CASCADE, FOREIGN KEY (`Pricefile_ID`) REFERENCES `Pricefile` (`Pricefile_ID`) ON UPDATE CASCADE, FOREIGN KEY (`File_ID`) REFERENCES `File` (`File_ID`) ON UPDATE CASCADE ) TYPE=InnoDB; # Host: 10.1.1.14 # Database: pricelink3 # Table: 'Pricefile' # CREATE TABLE `Pricefile` ( `Pricefile_ID` int(10) unsigned NOT NULL auto_increment, `ProductGroup` char(3) NOT NULL default '', `Affiliation_ID` int(10) unsigned NOT NULL default '0' PRIMARY KEY (`Pricefile_ID`), KEY `ProductGroup` (`ProductGroup`,`Affiliation_ID`), KEY `newtry` (`Affiliation_ID`), FOREIGN KEY (`ProductGroup`, `Affiliation_ID`) REFERENCES `Productgroup` (`ProductGroup`, `Affiliation_ID`) ON UPDATE CASCADE ) TYPE=InnoDB; ------------------------------------------------------------ Pricefile create has been trimmed to remove columns not of interest. Now previously all Item data was in a single table, with ProductGroup and Itemcode information in the same table. The old query I wanted to run needed an ORDER BY productgroup, itemcode Clause, and it could be easily optimised because the two columns were in the same table. In fact, the whole query was on one table and involved no joins, and data returned very quickly. Now here's my current query: ------------------------------------------------------------ -- SELECT Pricefile.Affiliation_ID AS AffiliationID, Item.Supplier_ID AS SupplierID, Pricefile.Pricefile_ID AS PricefileID, CONCAT(Pricefile.Productgroup, '-') AS ProductGroup, Item.ItemCode AS Itemcode, Item.Description AS Description, Item.ListPrice AS ListPrice, Item.Price1 AS Price1, Item.Price2 AS Price2, Item.Price3 AS Price3, Item.Price4 AS Price4, Item.Price5 AS Price5, Item.Cost AS Cost, Item.CoreCost AS CoreCost, Item.CoreSelling AS CoreSelling, Item.Weight AS Weight, Item.Package AS Package, Item.PriceEffectiveDate AS PriceEffectiveDate, Item.Popularity AS Popularity, Item.RowNum AS rowid, Item.LastChanged AS lastchanged, Item.URL as url, Item.Barcode AS barcode From Item, Pricefile WHERE Item.Pricefile_ID = Pricefile.Pricefile_ID AND Pricefile.Affiliation_ID = 1 ORDER BY Productgroup, Itemcode; ----------------------------------------------------------- And it has the following EXPLAIN: +-----------+------+----------------+--------------+-------- -+------------------------+------+-------------------------- -------+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +-----------+------+----------------+--------------+-------- -+------------------------+------+-------------------------- -------+ | Pricefile | ref | PRIMARY,newtry | newtry | 4 | const | 317 | Using temporary; Using filesort | | Item | ref | Pricefile_ID | Pricefile_ID | 4 | Pricefile.Pricefile_ID | 216 | | +-----------+------+----------------+--------------+-------- -+------------------------+------+-------------------------- -------+ Now to me the problem seems to be a matter of the sort. The temporary table is turning this query into a slow monster (there are 800,000 rows in the result set). Here's the SHOW INDEX for Pricefile: +-----------+------------+--------------+--------------+---- ------------+-----------+-------------+----------+--------+- -----+------------+---------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +-----------+------------+--------------+--------------+---- ------------+-----------+-------------+----------+--------+- -----+------------+---------+ | Pricefile | 0 | PRIMARY | 1 | Pricefile_ID | A | 673 | NULL | NULL | | BTREE | | | Pricefile | 1 | ProductGroup | 1 | ProductGroup | A | 673 | NULL | NULL | | BTREE | | | Pricefile | 1 | ProductGroup | 2 | Affiliation_ID | A | 673 | NULL | NULL | | BTREE | | | Pricefile | 1 | newtry | 1 | Affiliation_ID | A | 5 | NULL | NULL | | BTREE | | +-----------+------------+--------------+--------------+---- ------------+-----------+-------------+----------+--------+- -----+------------+---------+ Any advice/help that you may have would be greatly appreciated. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]