[SOLVED] Performance Issues

[mad4it]

MySQL version: 5.0.45

 

Hi,

 

I'm having problems with a database table - the structure is as follows :

 

 

CREATE TABLE `products_1` (

  `id` int(11) NOT NULL auto_increment,

  `product_name` varchar(200) default NULL,

  `product_code` varchar(50) default NULL,

  `product_category` varchar(200) default NULL,

  `uniqueid` varchar(13) default NULL,

  `product_price` decimal(10,2) default NULL,

  `xxxxpostage` decimal(15,2) default NULL,

  `product_importance` decimal(10,0) default NULL,

  `product_description` text,

  `product_discount` decimal(10,2) default NULL,

  `product_weight` decimal(10,2) default NULL,

  `product_subcategory` varchar(200) default NULL,

  `product_tax` char(3) default NULL,

  `product_stock` decimal(10,0) default NULL,

  `extra1` varchar(200) default NULL,

  `extra2` varchar(200) default NULL,

  `extra3` varchar(200) default NULL,

  `extra4` varchar(200) default NULL,

  `extra5` varchar(200) default NULL,

  KEY `id` (`id`),

  KEY `product_name` (`product_name`),

  KEY `product_category` (`product_category`,`product_subcategory`)

) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

 

 

The table contains appx 109,000 records and appears to be causing major CPU load issues. The table is searchable by either :

 

Product Name

Product Category

Product Category & Product SubCategory

Extra1

Extra2

Extra3

Extra5

 

The table previously contained appx 60,000 records and had no performance issues but since the size was increased from 60,000 to 109,000 the server has struggled to cope.

 

All ideas/suggestions welcome!

 

Thanks

 

 

 

[fenway]

It's not the size of the table, it's the nature of the query.... did you enable the slow-query-log?

[mad4it]

Hi,

 

Thanks - I enabled the slow-query-log but your comment caused me to examine the code and found that the main query the application was running was poorly indexed. I've corrected this and the problem now appears to be resolved!!