Page 1 of 1

How to speed up the Group By Clause for a large 3GB database

Posted: Fri Dec 31, 2010 1:07 am
by rahulephp
How to speed up the Group By Clause for a large 3GB database.

I am using Group by clause for a large database having 148 columns and 5 million rows with approx 3GB of size.
We need to apply the Group by clause on approx 1,00,000 rows at a time without using LIMIT.
We can’t use LIMIT as we need all of the entries from a category to be show in the filters section.

We have a Dedicated Linux server with 4GB RAM and latest Configuration with 2 processors.

I tried all different my.cnf configuration settings to optimize the mysql speed but nothing works.

Here is Query that I am using to fetch the data:

Code: Select all

SELECT e.product_id,
e.name,
e.description,
e.manufacturer,
e.imageurl,
e.warranty,
e.colour,
e.collection,
e.saleprice,
e.price,
e.ages,
e.size,
e.size_h,
e.size_w,
e.size_d,
e.size_unit,
e.wifi_ready,			 
e.bundled_deals_packages,
e.service_provider,
e.how_many_seats,
e.characters, 
e.publishercategory,
e.clean_modelno

MAX(price) as max_price,
MIN(price) as min_price,
count(distinct(advertiserid)) as total
FROM elec_products as e

WHERE status = 1
AND (subcategory2 = 3115) 
GROUP BY clean_modelno, publishercategory
ORDER BY total DESC

I have index on following columns
  • product_id PRIMARY KEY
  • Group_by(clean_modelno, publishercategory) BTREE
  • subcategory1 BTREE
  • subcategory2 BTREE
  • subcategory3 BTREE
  • subcategory4 BTREE
  • subcategory5 BTREE
  • status BTREE

Table Type is "MyISAM".


All major My.cnf configurations:
  • skip-locking
  • key_buffer_size = 512M
  • max_allowed_packet = 128M
  • table_open_cache = 512
  • sort_buffer_size = 128M
  • read_buffer_size = 128M
  • read_rnd_buffer_size = 128M
  • myisam_sort_buffer_size = 128M
  • thread_cache_size = 8
  • query_cache_size = 128M
  • max_heap_table_size=256M
  • tmp_table_size=256M
  • join_buffer_size = 2M
I can see lots of other similar Price Comparison website which has excellent pageload speed.
Please help me out from this and let me know if I am missing anything.

Re: How to speed up the Group By Clause for a large 3GB data

Posted: Fri Dec 31, 2010 3:50 am
by Christopher
With that size database and type of query I think it will always take some time. How often does the data change? It seems like you would be better to only run this query when the data changes and save the results in another table.

Re: How to speed up the Group By Clause for a large 3GB data

Posted: Fri Dec 31, 2010 4:12 am
by rahulephp
Thank you for taking part in this.

You know Christopher, You can compare this websites with other similar websites like Pricegrabber[dot]com OR kelkoo[dot]co[dot] uk
We need update the database almost everyday so it is not possible to create the another table for each category specially when we have 1500 categories.
It take approx. 2-3 minutes to fetch data from database if that particular category contain 100000 row which would be consider as huge pageload time and visitor will then close the browser and wont come again.

Any recommendations will be appreciated.

Re: How to speed up the Group By Clause for a large 3GB data

Posted: Fri Dec 31, 2010 4:59 am
by Darhazer
Please run explain on the query and show the result
This is the starting point of any optimization

Additionally, you can try the same without order by (wich will produce a 'filesort' in the explain results) to see if the ordering is not the slowest part of the execution (although the temporary needed because of the group by is more often the actual problem)

How much results returns this query? I see this is a query without a limit and this is not the wisest thing to do