MySQL Getting a count on two parameters

I have a table of orders, which contains a little over 200 million records. For statistics, I need to get three different counts. The first is the number of orders placed within a certain date range, then the number of orders placed from a specific state, and finally the number of orders from a specified state during a certain date range. The first two queries return 10-20 million records on average, and usually take under five seconds. However, I have not been able to get the third query to r开发者_运维知识库eturn results in under an hour. Here are the actual queries:

SELECT COUNT(*) 
  FROM orders 
 WHERE order_date BETWEEN date1 AND date2; 

The above has 10 mil records to summarize, query takes 4 seconds.

   SELECT COUNT(*) 
     FROM orders 
LEFT JOIN customers ON orders.customer_id = customers.customer_id 
    WHERE customer.state = 'PA'; 

The above has 15mil records to summarize, query takes 5 seconds.

(SELECT COUNT(*) 
   FROM orders 
  WHERE order_date BETWEEN date1 AND date2) 
UNION 
(SELECT COUNT(*) 
   FROM orders 
LEFT JOIN customers ON orders.customer_id=customers.customer_id
    WHERE customer.state = 'PA'); 

The above has 4500 records to summarize, query takes 2 hours.

Is there another approach I can take for that third query that would give me the count in a more reasonable amount of time? Preferably less than a minute?

Join the queries together. Also, change the LEFT JOIN to a INNER JOIN since you are filtering by the customer state.

SELECT COUNT(*) 
FROM orders 
    INNER JOIN customers ON orders.customer_id=customers.customer_id
WHERE customer.state = 'PA' AND order_date BETWEEN date1 AND date2

Your 3 queries should be.

SELECT COUNT(*) 
  FROM orders 
 WHERE order_date BETWEEN date1 AND date2; 

SELECT COUNT(*) 
      FROM orders 
INNER JOIN customers ON orders.customer_id = customers.customer_id 
     WHERE customer.state = 'PA'; 

SELECT COUNT(*) 
      FROM orders 
INNER JOIN customers ON orders.customer_id = customers.customer_id 
     WHERE customer.state = 'PA'
       AND order_date BETWEEN date1 AND date2;

The 2nd one can be a LEFT JOIN, but since you are filtering on customer using a WHERE clause, there is no reason to preserve any records from LEFT JOINing.

For what it's worth, your 3rd query that attempts to return counts from queries 1 and 2 in a single query is working slowly most likely because MySQL looks at the UNION And went all funny. Either of the below should work better

SELECT "Date", COUNT(*) 
   FROM orders 
  WHERE order_date BETWEEN date1 AND date2
UNION ALL
SELECT "Customer", COUNT(*) 
   FROM orders 
LEFT JOIN customers ON orders.customer_id=customers.customer_id
    WHERE customer.state = 'PA';

or

SELECT
(SELECT COUNT(*) 
   FROM orders 
  WHERE order_date BETWEEN date1 AND date2) DateCount,
(SELECT COUNT(*) 
   FROM orders 
LEFT JOIN customers ON orders.customer_id=customers.customer_id
    WHERE customer.state = 'PA') CustomerCount

(the second one returns it as two columns instead of 2 rows)