How to Efficiently Select One Row per Group from a Large Database Table?

Efficiently Selecting One Row from Each Group

In a large product table with millions of records, optimizing queries is crucial for performance. A common scenario is retrieving a single row from each group of a table based on a specific column, such as the maximum ID for each store.

Original Query and Performance Issue:

The initial query used for this task was:

SELECT id, product_name, store_id
FROM product
GROUP BY store_id
ORDER BY id.

However, this query faced performance issues due to the grouping and aggregation operations involved.

Recommended Approach:

An efficient solution is to use a subquery to determine the maximum ID for each store and join it to the main table:

SELECT  a.*
FROM    tableName a
        INNER JOIN
        (
            SELECT  store_ID, MAX(ID) max_ID
            FROM    tableName
            GROUP BY store_ID
        ) b ON a.store_ID = b.store_ID AND
                a.ID = b.max_ID

This query performs the grouping and aggregation in the subquery, resulting in better performance.

Additional Performance Tips:

• Ensure that the table has indexes on the ID and store_id columns.
• Consider using a database server with support for parallelization and optimization features.

Multiple Row Selection:

If multiple rows need to be retrieved from each group, the following query can be used:

SELECT ID, product_Name, store_ID
FROM   tableName a
WHERE
  (
     SELECT COUNT(*) 
     FROM   tableName b
     WHERE  b.store_ID = a.store_ID AND b.ID >= a.ID
  ) <= 2;

This query retrieves up to two rows from each group based on the ID column.

The above is the detailed content of How to Efficiently Select One Row per Group from a Large Database Table?. For more information, please follow other related articles on the PHP Chinese website!