MySQL Forums
Forum List  »  Optimizer & Parser

Re: Really need help with optimization
Posted by: Anders Bergman
Date: August 23, 2006 01:24PM

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;""

Options: Reply• Quote


Subject
Views
Written By
Posted
3332
August 22, 2006 01:43PM
1998
August 23, 2006 12:50AM
Re: Really need help with optimization
2071
August 23, 2006 01:24PM
2012
August 24, 2006 04:53AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.