List:General Discussion« Previous MessageNext Message »
From:mike Date:November 26 2003 6:37pm
Subject:Query Slowed With new normalized structure (sort across two tables)
View as plain text  
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.
Thread
Query Slowed With new normalized structure (sort across two tables)mike26 Nov