MySQL query taking too long


SELECT DATE_FORMAT( o.date_added,  '%m/%d/%Y' ) AS  'Date',             
       o.order_id as 'Order Number',
       CONCAT_WS(  " ",  `firstname` ,  `lastname` ) AS  `Name`, as 'Email', as 'Amount', 
       ot.value as 'Shipping'
FROM oc_order o
INNER JOIN oc_order_total ot ON ot.order_id = o.order_id
SELECT COUNT( email ) AS  'Orders Count', email
FROM oc_order
GROUP BY email
)xxx ON =
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( 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)

    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)


[Edit] Adding Explain After suggested changes:
EXPLAIN with Indexes added

Best Answer

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',