0

I have a table as below

id   fk_sales_id  status  created_at
1    001          5       2015-02-17 16:45:44
2    001          8       2015-02-18 18:45:44
3    002          30      2015-02-20 16:45:44
4    002          8       2015-02-18 18:45:44
5    001          30      2015-03-01 16:20:44
6    002          5       2015-03-17 16:45:44
7    002          20      2015-03-18 18:45:44
8    003          30      2015-03-10 16:45:44
9    003          8       2015-03-18 18:45:44
10   003          30      2015-03-21 16:20:44

I want the latest status of the each fk_sales_id and count how many of them in each status.

  • This table contain around 5 million records
  • We have 30 status
  • Each status I need to count them as below, items(only the latest records) stayed particular status for

    1 Day | 1-2 Days | 2-3 Days | 3-5 Days | 5-10 Days | 10-15 Days | 15 Days Beyond

So for one status my query as below, I need to execute this same query for 30 time to get full report.

SELECT 
SUM( CASE WHEN ((date(temp.max_created_at) > DATE_SUB(DATE(NOW()), INTERVAL 1 DAY)) AND (date(temp.max_created_at) <= DATE_SUB(DATE(NOW()), INTERVAL 0 DAY)))  THEN 1 ELSE 0 END ) AS INTERVAL0, 
SUM( CASE WHEN ((date(temp.max_created_at) > DATE_SUB(DATE(NOW()), INTERVAL 2 DAY)) AND (date(temp.max_created_at) <= DATE_SUB(DATE(NOW()), INTERVAL 1 DAY))) THEN 1 ELSE 0 END ) AS INTERVAL2, 
SUM( CASE WHEN ((date(temp.max_created_at) > DATE_SUB(DATE(NOW()), INTERVAL 3 DAY)) AND (date(temp.max_created_at) <= DATE_SUB(DATE(NOW()), INTERVAL 2 DAY))) THEN 1 ELSE 0 END ) AS INTERVAL3, 
SUM( CASE WHEN ((date(temp.max_created_at) > DATE_SUB(DATE(NOW()), INTERVAL 5 DAY)) AND (date(temp.max_created_at) <= DATE_SUB(DATE(NOW()), INTERVAL 3 DAY))) THEN 1 ELSE 0 END ) AS INTERVAL4, 
SUM( CASE WHEN ((date(temp.max_created_at) > DATE_SUB(DATE(NOW()), INTERVAL 10 DAY)) AND (date(temp.max_created_at) <= DATE_SUB(DATE(NOW()), INTERVAL 5 DAY))) THEN 1 ELSE 0 END ) AS INTERVAL5, 
SUM( CASE WHEN ((date(temp.max_created_at) > DATE_SUB(DATE(NOW()), INTERVAL 15 DAY)) AND (date(temp.max_created_at) <= DATE_SUB(DATE(NOW()), INTERVAL 10 DAY))) THEN 1 ELSE 0 END ) AS INTERVAL6, 
SUM( CASE WHEN ((date(temp.max_created_at) <= DATE_SUB(DATE(NOW()), INTERVAL 15 DAY))) THEN 1 ELSE 0 END ) AS INTERVAL7
FROM `status_history_table` AS sh_table
INNER JOIN (
    SELECT 
        MAX(created_at) AS max_created_at, sh_table2.status,
        MAX(id) AS max_soi_history_id,
        sh_table2.`fk_sales_id`
        FROM status_history_table as sh_table2  
        GROUP BY sh_table2.`fk_sales_id`
) AS temp 
ON sh_table.`id` = temp.max_soi_history_id AND  sh_table.`fk_sales_id` = temp.`fk_sales_id`
    where sh_table.`status` = 30;

For full report it takes 1 minute, but then I extracted below part(since its common for every query) into TEMPORARY TABLE(MEMORY) after report finished I delete the temporary table, then its takes only around 30s

SELECT 
MAX(created_at) AS max_created_at, sh_table2.status,
MAX(id) AS max_soi_history_id,
sh_table2.`fk_sales_id`
FROM status_history_table as sh_table2  
GROUP BY sh_table2.`fk_sales_id`

Temporary table takes close to 300mb,

Is there any other way to optimize this query and the whole procedure or different approach? My colleague says use memcached but if i implemented this way using memcached we cannot easily query memcached as per I know so it will be useless.

SHOW CREATE TABLE

CREATE TABLE `status_history_table` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'id of the status history',
  `fk_sales_id` int(10) unsigned NOT NULL COMMENT 'id of the sales order',
  `status` int(10) unsigned NOT NULL COMMENT 'New status',
  `created_at` datetime DEFAULT NULL COMMENT 'creation date ',
  PRIMARY KEY (`id_sales_order_item_status_history`),
  KEY `fk_new_status` (`status`),
  KEY `fk_sales_order_id` (`fk_sales_id`),
  KEY `index_soi_status` (`fk_status`,`fk_sales_id`),
  CONSTRAINT `ims_sales_order_status_history_ibfk_1` FOREIGN KEY     (`fk_sales_order`) REFERENCES `ims_sales_order` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION,
CONSTRAINT `status_history_table_ibfk_3` FOREIGN KEY (`status`) REFERENCES `ims_sales_order_status` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=5116120 DEFAULT CHARSET=utf8
4
  • How large is the table (number of rows) and how many distinct fk_sales_id are there? Mar 29 '15 at 12:54
  • Table contain around 5 millions records total, more than 1 million distinct fk_sales_id ids and it will grow.
    – Ntwobike
    Mar 29 '15 at 14:43
  • So, a sales_id has on average just 5 rows? Hard. Show us the output of SHOW CREATE TABLE status_history_table; Mar 29 '15 at 14:57
  • @ypercube i updated the question have a look, actual table have contain few extra columns i have removed them to simplify the question.
    – Ntwobike
    Mar 29 '15 at 16:05
1

First step: This is SQL. You don't need to make separated queries for every status type.

SELECT 
   sh_table.`status`,
   SUM( CASE WHEN ( date(temp.max_created_at) BETWEEN DATE_SUB(DATE(NOW()), INTERVAL 1 DAY) AND DATE_SUB(DATE(NOW()), INTERVAL 0 DAY)) THEN 1 ELSE 0 END ) AS INTERVAL1,
   SUM( CASE WHEN ( date(temp.max_created_at) BETWEEN DATE_SUB(DATE(NOW()), INTERVAL 2 DAY) AND DATE_SUB(DATE(NOW()), INTERVAL 1 DAY)) THEN 1 ELSE 0 END ) AS INTERVAL2,
   SUM( CASE WHEN ( date(temp.max_created_at) BETWEEN DATE_SUB(DATE(NOW()), INTERVAL 3 DAY) AND DATE_SUB(DATE(NOW()), INTERVAL 2 DAY)) THEN 1 ELSE 0 END ) AS INTERVAL3,
   SUM( CASE WHEN ( date(temp.max_created_at) BETWEEN DATE_SUB(DATE(NOW()), INTERVAL 5 DAY) AND DATE_SUB(DATE(NOW()), INTERVAL 3 DAY)) THEN 1 ELSE 0 END ) AS INTERVAL4,
   SUM( CASE WHEN ( date(temp.max_created_at) BETWEEN DATE_SUB(DATE(NOW()), INTERVAL 10 DAY) AND DATE_SUB(DATE(NOW()), INTERVAL 5 DAY)) THEN 1 ELSE 0 END ) AS INTERVAL5,
   SUM( CASE WHEN ( date(temp.max_created_at) BETWEEN DATE_SUB(DATE(NOW()), INTERVAL 15 DAY) AND DATE_SUB(DATE(NOW()), INTERVAL 10 DAY)) THEN 1 ELSE 0 END ) AS INTERVAL6,
   SUM( CASE WHEN ((date(temp.max_created_at) <= DATE_SUB(DATE(NOW()), INTERVAL 15 DAY))) THEN 1 ELSE 0 END ) AS INTERVAL7
FROM `status_history_table` AS sh_table,
  ( SELECT 
        MAX(created_at) AS max_created_at, sh_table2.status,
        MAX(id) AS max_soi_history_id,
        sh_table2.`fk_sales_id`
        FROM status_history_table as sh_table2  
        GROUP BY sh_table2.`fk_sales_id` ) AS temp 
WHERE sh_table.`id` = temp.`max_soi_history_id`
AND sh_table.`fk_sales_id` = temp.`fk_sales_id`
GROUP BY sh_table.`status`;
1
  • yes its reduced the time by more than half.
    – Ntwobike
    Mar 30 '15 at 8:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.