MySQL Server - CRE Loaded - 150-250 Customer Groups - Server Hangs

what are you trying to do on your page? print 400K price in one page? if yes, implement paging or filter the data by product...

also instead of adding p records into pricving table for each customer, just put the price if it is different than org price (which is in products table)
or if there is a formula (like 10% discount for a customer for all products) then no need for pricing table, just products and customer is enough...

anyway, adding an index to group_pricing table may help PK (prdid, custid) and another index (custid, prdid) may help...

what does hangs mean? no display at all or it displays in 10 minutes or gives error msg after a while? maybe you are doing something wrong in your page, like infinite loops...

Hi, HainKurt.

The page displays up to 75 products in a list.
No 400K on one page. )
...I'll reduce that to 25 or so - thanks for that.

No % discounts - all custom pricing, but sometimes it only is 5 or 6 products out of the 2000, so I will implement your next suggestions as well (insert custom price record only if different from original).

Thanks for the suggestions on which columns to index - we'll try that too!

Hang = No response at all. Getting error message that MySQL has disappeared.

I'll let you know how it goes.

If you want to post the CREATE TABLE statements here, I will be glad to take a look.

You should have indexes on every column used in WHERE, JOIN, ORDER BY and GROUP BY.

You can use EXPLAIN to see what the queries are doing.

Is the "Customer Groups Pricing" table a pivot table?  Have you installed ySlow?

Just a quick update on progress:

We scripted the removal of all records that were the same as the original product pricing.

It has not significantly improved speed, although there is a noticeable difference (not quite *improvement*) but it is in the right direction.

Tomorrow we are going to look at the SEARCH Page MySQL Queries (the slowest of them all when we have lots of Custom Pricing) and check all the stuff you mention, Ray.

Thanks, you guys.

- Dasher

G'luck!

Here is some more information. Basically...we have made no major progress, yet.
-------------------------------------------------------------
here is slightly simplified search query:

SELECT DISTINCT p.products_image, p.products_model, m.manufacturers_name, m.manufacturers_id, p.products_id, pd.products_name, p.products_price, p.products_tax_class_id, p.products_price AS final_price
FROM
(((products p LEFT JOIN products_groups pg ON p.products_id = pg.products_id)
LEFT JOIN specials s ON p.products_id = s.products_id)
LEFT JOIN manufacturers m ON p.manufacturers_id = m.manufacturers_id), products_description pd
WHERE p.products_status = '1'
AND p.products_id = pd.products_id
AND pd.language_id = '1'
AND (MATCH (pd.products_name, pd.products_description) AGAINST ('bread*' IN boolean MODE) OR p.products_model = 'bread')

in the last line it performs search in 2 tables - 'products_description' and 'products'.
'products_description' has fulltext index for given 2 columns, 'products' doesn't seem to need one.

i tried to remove parts of search expression - run this query ending like this -
AND (MATCH (pd.products_name, pd.products_description) AGAINST ('bread*' IN boolean MODE))

..and like this -
AND (p.products_model = 'bread')

search in tables separately is ultra-fast (<0.1s for both tables), and search in both at once is sloooow (3.5s average)


--
-- Table structure for table `products`
--

CREATE TABLE IF NOT EXISTS `products` (
  `products_id` int(11) NOT NULL auto_increment,
  `products_quantity` int(4) NOT NULL default '0',
  `products_model` varchar(25) default NULL,
  `products_image` varchar(64) default NULL,
  `products_image_med` varchar(64) default NULL,
  `products_image_lrg` varchar(64) default NULL,
  `products_image_sm_1` varchar(64) default NULL,
  `products_image_xl_1` varchar(64) default NULL,
  `products_image_sm_2` varchar(64) default NULL,
  `products_image_xl_2` varchar(64) default NULL,
  `products_image_sm_3` varchar(64) default NULL,
  `products_image_xl_3` varchar(64) default NULL,
  `products_image_sm_4` varchar(64) default NULL,
  `products_image_xl_4` varchar(64) default NULL,
  `products_image_sm_5` varchar(64) default NULL,
  `products_image_xl_5` varchar(64) default NULL,
  `products_image_sm_6` varchar(64) default NULL,
  `products_image_xl_6` varchar(64) default NULL,
  `products_price` decimal(15,4) NOT NULL default '0.0000',
  `products_date_added` datetime NOT NULL default '0000-00-00 00:00:00',
  `products_last_modified` datetime NOT NULL default '0000-00-00 00:00:00',
  `products_date_available` datetime NOT NULL default '0000-00-00 00:00:00',
  `products_weight` decimal(5,2) NOT NULL default '0.00',
  `products_status` tinyint(1) NOT NULL default '0',
  `products_tax_class_id` int(11) NOT NULL default '0',
  `manufacturers_id` int(11) default NULL,
  `products_ordered` int(11) NOT NULL default '0',
  `products_parent_id` int(11) NOT NULL default '0',
  `products_price1` decimal(15,2) NOT NULL default '0.00',
  `products_price2` decimal(15,2) NOT NULL default '0.00',
  `products_price3` decimal(15,2) NOT NULL default '0.00',
  `products_price4` decimal(15,2) NOT NULL default '0.00',
  `products_price5` decimal(15,2) NOT NULL default '0.00',
  `products_price6` decimal(15,2) NOT NULL default '0.00',
  `products_price7` decimal(15,2) NOT NULL default '0.00',
  `products_price8` decimal(15,2) NOT NULL default '0.00',
  `products_price9` decimal(15,2) NOT NULL default '0.00',
  `products_price10` decimal(15,2) NOT NULL default '0.00',
  `products_price11` decimal(15,2) NOT NULL default '0.00',
  `products_price1_qty` int(11) NOT NULL default '0',
  `products_price2_qty` int(11) NOT NULL default '0',
  `products_price3_qty` int(11) NOT NULL default '0',
  `products_price4_qty` int(11) NOT NULL default '0',
  `products_price5_qty` int(11) NOT NULL default '0',
  `products_price6_qty` int(11) NOT NULL default '0',
  `products_price7_qty` int(11) NOT NULL default '0',
  `products_price8_qty` int(11) NOT NULL default '0',
  `products_price9_qty` int(11) NOT NULL default '0',
  `products_price10_qty` int(11) NOT NULL default '0',
  `products_price11_qty` int(11) NOT NULL default '0',
  `products_qty_blocks` int(11) NOT NULL default '1',
  `products_group_access` varchar(50) NOT NULL default 'G,0',
  `products_nav_access` varchar(255) NOT NULL default 'G,0',
  `products_ready_to_ship` tinyint(1) NOT NULL default '0',
  `sort_order` smallint(3) NOT NULL default '0',
  `vendors_id` int(11) default '1',
  `vendors_product_price` decimal(15,4) NOT NULL default '0.0000',
  `vendors_prod_id` varchar(24) NOT NULL default '',
  `vendors_prod_comments` text,
  `products_ship_price` decimal(15,2) NOT NULL default '0.00',
  PRIMARY KEY  (`products_id`),
  KEY `idx_products_date_added` (`products_date_added`),
  KEY `modelnumber` (`products_model`(10))
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3437 ;


--
-- Table structure for table `products_description`
--

CREATE TABLE IF NOT EXISTS `products_description` (
  `products_id` int(11) NOT NULL auto_increment,
  `language_id` int(11) NOT NULL default '1',
  `products_name` varchar(64) NOT NULL default '',
  `products_blurb` text,
  `products_description` text,
  `products_url` varchar(255) default NULL,
  `products_url2` varchar(255) default NULL,
  `products_viewed` int(5) default '0',
  `products_head_title_tag` varchar(80) default NULL,
  `products_head_desc_tag` longtext NOT NULL,
  `products_head_keywords_tag` longtext NOT NULL,
  PRIMARY KEY  (`products_id`,`language_id`),
  KEY `products_name` (`products_name`(20)),
  KEY `descrip` (`products_description`(10)),
  FULLTEXT KEY `idx_name_desc` (`products_name`,`products_description`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3437 ;

Hey, this is not abandoned. We have just not figured out our solution yet....working on it....

I found a part of the MySQL search query that referred to the Customer Groups Pricing tables.

Since these prices are irrelevant to the search results in our case, I just commented that part out so it just searches the products tables for string matches.

Thanks, Ray.

I just hacked a solution myself, in my opinion.
Ray helped point me in the right direction.
Ultimately, there is probably a more correct solution.
I hope this helps someone, though!