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

[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:

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, publishercatego ry) 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_pac ket = 128M
• table_open_cach e = 512
• sort_buffer_siz e = 128M
• read_buffer_siz e = 128M
• read_rnd_buffer _size = 128M
• myisam_sort_buf fer_size = 128M
• thread_cache_si ze = 8
• query_cache_siz e = 128M
• max_heap_table_ size=256M
• tmp_table_size= 256M
• join_buffer_siz e = 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.

[code green]

It may not help that it is not legal SQL, but I am not sure by how much the performance is affected.
You are using aggregate functions in your SELECT for three fields,
COUNT(), MAX(), MIN()
and a GROUP BY on clean_modelno, publishercatego ry.
So how does MySQL 'know' which entry of the following to return?

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

Well it runs an algorithm in its engine to sort this out.
The point I am trying to make is this query will throw an error in SQL server and others, but in MySQL we can get away with it.
Does this slow things down? I don't know, but may be worth thinking about

[rahulephp]

Thank you for taking part in this.

Sorry, their was a typo, You need to put a COMMA after e.clean_modelno ,(here)

e.publishercategory,
e.clean_modelno,
 
MAX(price) as max_price,
MIN(price) as min_price,

I am getting the expected results that i want but it takes huge time to make group of 100000 products by their clean_modelno, publishercatego ry


You know code green, 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.

Here is Index structure:



Any recommendations will be appreciated.

[rahulephp]

Thank you for taking part in this.

Sorry, their was a typo, You need to put a COMMA after e.clean_modelno ,(here)

e.publishercategory,
e.clean_modelno,
 
MAX(price) as max_price,
MIN(price) as min_price,

I am getting the expected results that i want but it takes huge time to make group of 100000 products by their clean_modelno, publishercatego ry


You know code green, 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.

Here is Index structure:



Any recommendations will be appreciated.