What is the fastest way to run this MySQL query that is sorted by count's increase & decrease?

I have a table that records every single sales for 350+ products in a store; column includes product name and date.

I am working on a page that lists the fastest moving product based on the percentage of sale开发者_如何转开发s increase, but the query I have is very slow considering there are 350+ products and 60000+ records.

  SELECT product_name, date 
    FROM data 
ORDER BY ((SELECT COUNT(ID) 
             FROM data 
            WHERE date='$date2') - 
          (SELECT COUNT(ID) 
             FROM data 
            WHERE date='$date1')) / 
          (SELECT COUNT(ID) 
             FROM data 
            WHERE date='$date1') DESC

My MySQL knowledge is quite limited, so any help from the community will be appreciated.

Data sample:

| id | product_name    | date       |
| 1  | pen             | 2011-04-22 |
| 2  | pencil          | 2011-04-22 |
| 3  | pen             | 2011-04-23 |
| 4  | pen             | 2011-04-23 |
| 5  | pencil          | 2011-04-23 |
| 6  | pen             | 2011-04-23 |
| 7  | pencil          | 2011-04-23 |

Expected output:

1  Pen     200%  (3-1)/1
2  Pencil  100%  (2-1)/1

---

Eliminate a double select
If you create a stored function, like so:

DELIMITER $$

CREATE FUNCTION SalesIncrease(OldSales integer, NewSales integer)
  RETURNS float
BEGIN
  DECLARE Result float;
  IF OldSales = 0 THEN SET result = 1; /*100%*/
  ELSE BEGIN
    IF NewSales = 0 THEN SET result = -1; /*-100%*/ 
    ELSE SET Result = (NewSales - OldSales) / OldSales;
    END IF;
  END; END IF;

  RETURN result;
END $$

DELIMITER ;

Now change the query to

//don't forget to escape $date1 and $date2 to protect against SQL-injection
$date1 = mysql_real_escape_string($date1);
$date2 = mysql_real_escape_string($date2);

$sql_query = "SELECT oldsales.product_name, oldsales.date, 
  SalesIncrease( COUNT(oldsales.ID), COUNT(newsales.ID)) as Increase 
FROM data AS oldsales
LEFT join data AS newsales ON (oldsales.id = newsales.id)
WHERE oldsales.date = '$date1' AND newsales.date = '$date2'
ORDER BY Increase ";

---

SELECT COALESCE(d2.product_name, d1.product_name) AS product_name, 
       COALESCE(100*(COALESCE(d2.n, 0)/d1.n -1), 'Infinity') AS pct_increase
FROM (SELECT product_name, COUNT(id) AS n FROM data WHERE date=$date1
     GROUP BY product_name) as d1 
FULL OUTER JOIN 
     (SELECT product_name, COUNT(id) AS n FROM data WHERE date=$date2
     GROUP BY product_name) as d2
ON d1.product_name=d2.product_name
ORDER BY 2 DESC; 
/* BY 2 == second column, nonstandard SQL but works. Can also copy column expression */

Make sure you have an index on product_name and an index on date.

The COALESCE is to take care of the edge cases of no sales on one of the dates, as is the use of an OUTER JOIN.