• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 421
  • Last Modified:

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

We have a customer that has custom pricing for each customer at the product level.

We decided to program a way for each customer to be their own Customer Group.

There are 2000 products, and about 200 Customers.
So that's 400,000 records in the Customer Groups Pricing data table.

When we go to browse the site after loading this data, the site hangs.
It's obvious that this quantity of data is the cause.

Does anyone have some suggestions on a way to better handle this situation?

Would rebuilding indexes help?
Reduce the data amount so only *different from retail* prices are imported?

Any suggestions would be most appreciated.

Thanks,
Dasher
0
dasher3000
Asked:
dasher3000
  • 6
  • 3
  • 2
2 Solutions
 
HainKurtSr. System AnalystCommented:
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...
0
 
dasher3000Author Commented:
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.
0
 
HainKurtSr. System AnalystCommented:
you should post involving queries in the page...
there must be something weird in your queries which needs to be re-written I guess...
with those number of records no query should hang/kill the server ;)
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
Ray PaseurCommented:
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?
0
 
dasher3000Author Commented:
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
0
 
Ray PaseurCommented:
G'luck!
0
 
dasher3000Author Commented:
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 ;

0
 
Ray PaseurCommented:
Well, I don't see anything that looks like a "Customer Groups Pricing" table there, but there may be some germ of an idea in the queries.

Try running EXPLAIN on that query and see what you get.  Also, try running it without DISTINCT and see if the time is different.  IIRC, SELECT DISTINCT may cause repeated table scans - perhaps EXPLAIN will give a clue about a better way to formulate the query.

Another strategy I have used successfully with long-running queries is to do a "down-select" copying part of a larger results set into a temporary table in memory.  Then the complicated activities can be performed against a smaller subset of the data.

Another strategy you might consider is the use of soundex() and metaphone() values for keyword matching.  You might be able to use these instead of a FULLTEXT index.
0
 
dasher3000Author Commented:
Hey, this is not abandoned. We have just not figured out our solution yet....working on it....
0
 
dasher3000Author Commented:
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.
0
 
dasher3000Author Commented:
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!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 6
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now