Re: Really need help with optimization
I have changed the varchar(80) to varchar (100) and removed all old indexes and added some new ones. And guess what !?! The function is working hundreds of times faster ! Thank you for the tips about what to do. :)
When i see the EXPLAIN results it seems to be a lot left to do for performance but it already has good speed compared to before.
The tables looks like this now:
CREATE TABLE `tblPriceCategory` (
`Id` int(11) NOT NULL auto_increment,
`sProductName` varchar(100) NOT NULL default '',
`sCategoryName` varchar(50) default NULL,
`sManufacturerName` varchar(50) default NULL,
PRIMARY KEY (`Id`),
KEY `Index1` (`sProductName`),
KEY `Index2` (`sCategoryName`),
KEY `Index3` (`sProductName`,`sCategoryName`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
CREATE TABLE `tblPriceData` (
`Id` int(11) NOT NULL auto_increment,
`sShopLink` varchar(70) NOT NULL default '0',
`sProductTitle` varchar(100) NOT NULL default '0',
`sSale` varchar(5) default '0',
`dblCurrentPrice` double default NULL,
`dblOldPrice` double default NULL,
`sStockStatus` varchar(50) default NULL,
`sDirectLink` varchar(250) default '0',
`dPriceDate` datetime default '0000-00-00 00:00:00',
PRIMARY KEY (`Id`),
KEY `Index1` (`sProductTitle`),
KEY `Index2` (`sShopLink`),
KEY `Index3` (`sSale`),
KEY `Index4` (`sProductTitle`,`sShopLink`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='Contains raw data from shops';
Probably there is a lot of more optimizations left so please feel free to give me tips.
The following EXPLAIN SELECT gives me:
EXPLAIN SELECT tblPriceData.sProductTitle,tblPriceData.sShopLink,tblPriceData.sSale,tblPriceData.dblCurrentPrice,tblPriceData.dblOldPrice,tblPriceData.sStockStatus,tblPriceData.sDirectLink,tblPriceData.dPriceDate,tblPriceData.ID,tblPriceCategory.sCategoryName
FROM tblPriceData
LEFT JOIN tblPriceCategory ON tblPriceData.sProductTitle=tblPriceCategory.sProductName
WHERE tblPriceCategory.sCategoryName = "Rullar - Multi" GROUP BY tblPriceData.sProductTitle, tblPriceData.sShopLink, tblPriceData.dblCurrentPrice
ORDER BY tblPriceData.sProductTitle,tblPriceData.dblCurrentPrice
LIMIT 200 OFFSET 0
1;"SIMPLE";"tblPriceData";"ref";"Index1,Index4";"Index1";100;"fiske_forum.tblPriceCategory.sProductName";2;""
1;"SIMPLE";"tblPriceCategory";"ref";"Index1,Index2,Index3";"Index2";51;"const";485;"Using where; Using temporary; Using filesort"
EXPLAIN SELECT tblPriceData.sProductTitle,tblPriceData.sShopLink,tblPriceData.sSale,tblPriceData.dblCurrentPrice,tblPriceData.dblOldPrice,tblPriceData.sStockStatus,tblPriceData.sDirectLink,tblPriceData.dPriceDate,tblPriceData.ID,tblPriceCategory.sCategoryName
FROM tblPriceData
LEFT JOIN tblPriceCategory ON tblPriceData.sProductTitle=tblPriceCategory.sProductName
WHERE tblPriceData.sProductTitle LIKE "%ambassadeur%"
GROUP BY tblPriceData.sProductTitle, tblPriceData.sShopLink, tblPriceData.dblCurrentPrice
ORDER BY tblPriceData.sProductTitle,tblPriceData.dblCurrentPrice
LIMIT 200 OFFSET 0
1;"SIMPLE";"tblPriceData";"ALL";NULL;NULL;NULL;NULL;53410;"Using where; Using temporary; Using filesort"
1;"SIMPLE";"tblPriceCategory";"ref";"Index1,Index3";"Index1";100;"fiske_forum.tblPriceData.sProductTitle";1;""
EXPLAIN SELECT tblPriceData.sProductTitle,tblPriceData.sShopLink,tblPriceData.sSale,tblPriceData.dblCurrentPrice,tblPriceData.dblOldPrice,tblPriceData.sStockStatus,tblPriceData.sDirectLink,tblPriceData.dPriceDate,tblPriceData.ID,tblPriceCategory.sCategoryName
FROM tblPriceData
LEFT JOIN tblPriceCategory ON tblPriceData.sProductTitle=tblPriceCategory.sProductName
GROUP BY tblPriceData.sProductTitle, tblPriceData.sShopLink, tblPriceData.dblCurrentPrice
ORDER BY tblPriceData.sProductTitle,tblPriceData.dblCurrentPrice
LIMIT 200 OFFSET 0
1;"SIMPLE";"tblPriceData";"ALL";NULL;NULL;NULL;NULL;53410;"Using temporary; Using filesort"
1;"SIMPLE";"tblPriceCategory";"ref";"Index1,Index3";"Index1";100;"fiske_forum.tblPriceData.sProductTitle";1;""