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]
