Jump to content

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


rahulephp

Recommended Posts

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:

 

 

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.

 

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.