MySQL query taking too long

Question

SELECT DATE_FORMAT( o.date_added,  '%m/%d/%Y' ) AS  'Date',             
       o.order_id as 'Order Number',
       CONCAT_WS(  " ",  `firstname` ,  `lastname` ) AS  `Name`,
       o.email as 'Email',
       o.total as 'Amount', 
       ot.value as 'Shipping'
FROM oc_order o
INNER JOIN oc_order_total ot ON ot.order_id = o.order_id
JOIN (
SELECT COUNT( email ) AS  'Orders Count', email
FROM oc_order
GROUP BY email
)xxx ON xxx.email = o.email
WHERE xxx.`Orders Count` >3
AND ot.code =  'shipping'
AND o.order_status_id !=  '0'
ORDER BY  `o`.`email` ASC

In this query, I am trying to get list of orders placed by customers more than 3 times. Email is the field by which I can count orders. Couple of problems with this query though:

1. Query takes 150 seconds to pull up 24K orders. How can I improve it to make it quicker?
2. I would Like to display number of orders by a customer in select but when I do count(xxxx.email) it is displaying just 1 row and counting all orders not just 1 customer's.
   EXPLAIN SELECT and Create table
   Create Table for oc_order

    CREATE TABLE oc_order (
   order_id int(11) NOT NULL AUTO_INCREMENT,
   invoice_no int(11) NOT NULL,
   invoice_prefix varchar(26) NOT NULL,
   store_id int(11) NOT NULL,
   store_name varchar(64) NOT NULL,
   store_url varchar(255) NOT NULL,
   customer_id int(11) NOT NULL,
   customer_group_id int(11) NOT NULL,
   firstname varchar(32) NOT NULL,
   lastname varchar(32) NOT NULL,
   email varchar(96) NOT NULL,
   telephone varchar(32) NOT NULL,
   fax varchar(32) NOT NULL,
   custom_field text NOT NULL,
   payment_firstname varchar(32) NOT NULL,
   payment_lastname varchar(32) NOT NULL,
   payment_company varchar(40) NOT NULL,
   payment_address_1 varchar(128) NOT NULL,
   payment_address_2 varchar(128) NOT NULL,
   payment_city varchar(128) NOT NULL,
   payment_postcode varchar(10) NOT NULL,
   payment_country varchar(128) NOT NULL,
   payment_country_id int(11) NOT NULL,
   payment_zone varchar(128) NOT NULL,
   payment_zone_id int(11) NOT NULL,
   payment_address_format text NOT NULL,
   payment_custom_field text NOT NULL,
   payment_method varchar(128) NOT NULL,
   payment_cost decimal(15,4) NOT NULL DEFAULT '0.0000',
   payment_code varchar(128) NOT NULL,
   shipping_firstname varchar(32) NOT NULL,
   shipping_lastname varchar(32) NOT NULL,
   shipping_company varchar(40) NOT NULL,
   shipping_address_1 varchar(128) NOT NULL,
   shipping_address_2 varchar(128) NOT NULL,
   shipping_city varchar(128) NOT NULL,
   shipping_postcode varchar(10) NOT NULL,
   shipping_country varchar(128) NOT NULL,
   shipping_country_id int(11) NOT NULL,
   shipping_zone varchar(128) NOT NULL,
   shipping_zone_id int(11) NOT NULL,
   shipping_address_format text NOT NULL,
   shipping_custom_field text NOT NULL,
   shipping_method varchar(128) NOT NULL,
   shipping_cost decimal(15,4) NOT NULL DEFAULT '0.0000',
   shipping_code varchar(128) NOT NULL,
   comment text NOT NULL,
   total decimal(15,4) NOT NULL DEFAULT '0.0000',
   extra_cost decimal(15,4) NOT NULL DEFAULT '0.0000',
   order_status_id int(11) NOT NULL,
   affiliate_id int(11) NOT NULL,
   commission decimal(15,4) NOT NULL,
   marketing_id int(11) NOT NULL,
   tracking varchar(64) NOT NULL,
   language_id int(11) NOT NULL,
   currency_id int(11) NOT NULL,
   currency_code varchar(3) NOT NULL,
   currency_value decimal(15,8) NOT NULL DEFAULT '1.00000000',
   ip varchar(40) NOT NULL,
   forwarded_ip varchar(40) NOT NULL,
   user_agent varchar(255) NOT NULL,
   accept_language varchar(255) NOT NULL,
   date_added datetime NOT NULL,
   date_modified datetime NOT NULL,
   payment_company_id varchar(32) NOT NULL,
   payment_tax_id varchar(32) NOT NULL,
   PRIMARY KEY (order_id),
   KEY store_id (store_id),
   KEY customer_id (customer_id),
   KEY customer_group_id (customer_group_id),
   KEY payment_country_id (payment_country_id),
   KEY payment_zone_id (payment_zone_id),
   KEY shipping_country_id (shipping_country_id),
   KEY shipping_zone_id (shipping_zone_id),
   KEY order_status_id (order_status_id),
   KEY affiliate_id (affiliate_id),
   KEY marketing_id (marketing_id),
   KEY language_id (language_id),
   KEY currency_id (currency_id),
   KEY payment_company_id (payment_company_id),
   KEY payment_tax_id (payment_tax_id),
   KEY customer_id_2 (customer_id),
   KEY customer_id_3 (customer_id),
   KEY superdruid_order (date_added,order_status_id,order_id)
   ) ENGINE=InnoDB AUTO_INCREMENT=123456 DEFAULT CHARSET=utf8

   
   Create Table for oc_order_total

   CREATE TABLE oc_order_total (
   order_total_id int(10) NOT NULL AUTO_INCREMENT,
   order_id int(11) NOT NULL,
   code varchar(32) NOT NULL,
   title varchar(255) NOT NULL,
   value decimal(15,4) NOT NULL DEFAULT '0.0000',
   sort_order int(3) NOT NULL,
   text varchar(255) NOT NULL,
   PRIMARY KEY (order_total_id),
   KEY order_id (order_id)
   ) ENGINE=InnoDB AUTO_INCREMENT=123456 DEFAULT CHARSET=utf8

   
   

[Edit] Adding Explain After suggested changes:

Answer 1

To do the filtering sooner, change the subquery to

( SELECT email,
         COUNT(*) AS email_ct
      FROM oc_order
      GROUP BY  email
      HAVING COUNT(*) > 3 ) AS xxx

Then remove xxx.`Orders Count` >3 as being redundant.

To make the subquery run faster, oc_orders needs INDEX(email)

In oc_order_total, change KEY order_id (order_id) to KEY order_id (order_id, code)

With email_ct, you can add this to the main part of the query:

email_ct as 'Orders Count',

Answer 2

Normalize your database. The fact that one order is "self sustaining" and hence has a very simple insertion (you just let the web application copy data from the previous orders) has a negative effect on data extraction. Break your table in its basic components for example the table store would look like:

store_id int(11) NOT NULL,
store_name varchar(64) NOT NULL,
store_url varchar(255) NOT NULL

Also you are going to have a table customer, a table payment (one customer having multiple payments) and a table shipping (one customer has multiple shippings)

Order total has the same flaw. Have an order total table (without order_id), and either have the order_total_id on the order (if an order can only belong to one order total) or have a third table order2order_total which will emulate the many to many relation.

Simply by having the record shorter you gain significantly on performance.

Email is not the key of the customer (in the way the table is now designed, a customer may provide a fresh email on each order), so your group by criteria shall be "customer_id". Group By benefits of existing indexes, this will also speed up the query, especially if you apply the excellent hint of filtering early provided in the previous answer.