Query Speed (Fulltext + Group By) on Very large Database

Hi David

I have about 2.3 mil rows in the products table.

Queries that are starting to slow down are the Fulltext search.
The optimizer uses the index for the Fulltext search then creates a temp table and does the GROUP BY which causes the slowdown.
Query completes in under 1 sec if I remove the Group By clause.

Im eager to learn how I can speed the script up. I plan to import over 3mil products soon, so will need to get the script running very smoothly.

Some Wild ideas I thought of:

### Master / Child product

I thought I could get rid of the group by if I had 1 master product that contained the number of child products (duplicates).
These master products would be set by running a once off update upon importing.
Something like:
$sql = "SELECT name, COUNT(id) AS pcount FROM `".$config_databaseTablePrefix."products` GROUP BY name HAVING pcount > 1";
Then looping through each result:
$sql = "UPDATE `".$config_databaseTablePrefix."products` SET master=1, no_products = '{$product['pcount']}' WHERE name = '{$product['name']}' ORDER price ASC BY LIMIT 1";

The only issue with this is running a update query on millions of rows is going to take ages.
Maybe you another way around this? Create a table then drop and rename?

Do you have any idea how the big comparison engines do it?

Any help would be appreciated..