Translating PHP function to SQL, sometimes the balance becomes 0


We have a very big PHP function that gets all the orders we have to renew, it takes between 2h and 9h to finish depending on the server and whether it’s running PHP 5 or 7.

None of the queries are in the slow query log except the first query which gets all the orders and takes 40s, after that, all the filtering is done through PHP and that’s why I’m trying to rewrite the whole thing in SQL

The main query of our function is this

(SELECT * FROM orders WHERE extended_duration=0 AND extend_access!=1 AND is_paid=1 AND duration!=0 AND is_renew=1 AND was_renewed=0 AND object_name IS NULL AND (service_id IS NOT NULL OR bundle_id IS NOT NULL OR pkg_id_fk IS NOT NULL) AND DATE_ADD(paid_on, INTERVAL duration HOUR)< NOW() AND DATE(DATE_ADD(paid_on, INTERVAL duration HOUR)) BETWEEN DATE(NOW() - INTERVAL 15 DAY) AND DATE(NOW()) GROUP BY username,service_id,bundle_id,object_name,pkg_id_fk) UNION  (SELECT * FROM orders WHERE extended_duration!=0 AND extend_access!=1 AND is_paid=1 AND duration!=0 AND is_renew=1 AND was_renewed=0 AND object_name IS NULL AND (service_id IS NOT NULL OR bundle_id IS NOT NULL OR pkg_id_fk IS NOT NULL) AND DATE_ADD(paid_on, INTERVAL extended_duration HOUR)< NOW() AND DATE(DATE_ADD(paid_on, INTERVAL extended_duration HOUR)) BETWEEN DATE(NOW() - INTERVAL 15 DAY) AND DATE(NOW())  GROUP BY username,service_id,bundle_id,object_name,pkg_id_fk)"); 

After that we start filtering, one of the things we do is check if the user signed up for a free trial then we have to renew his plan for another paid plan

if ($  order['renewal_price_id'] != 0){     $  order['price_id'] = $  order['renewal_price_id']; }  $  check_if_active_plan = $  this->db->prepare("SELECT * FROM prices WHERE id=:price_id LIMIT 1");  $  sub_plan = $  check_if_active_plan->fetch(PDO::FETCH_ASSOC); 

And also we check if he has a discount and update the balance accordingly, I have problems with translating this line to SQL

 if ($  sub_plan['discount_rate'] > 0 && $  sub_plan['discount_start'] <= date("Y-m-d") && $  sub_plan['discount_end'] >= date("Y-m-d")) {   $  orders[$  i]['balance'] = $  sub_plan['balance'] - ($  sub_plan['balance'] * $  sub_plan['discount_rate'] / 100);  } 

On the other hand, My SQL query takes between 20 and 40 seconds to finish on a big data set in MySQL 8, but I have two problems:

  • Whenever I try to update the balance for someone who has a free trial or a discount, it sets the balance to 0
  • My query should get either the same number of results as the query above or less due to the filtering, my query returns more results after all the filtering is done.

DBFidle, My Query

(   SELECT     ord.orderid,     ord.username,     ord.msisdn,     ord.order_type,     ord.bundle_id,     ord.service_id,     ord.object_name,     ord.app_id,     ord.is_paid,     ord.is_renew,     ord.is_test,     ord.extended_duration,     ord.created,     ord.paid_on,     ord.was_renewed,     ord.renew_orderid,     ord.renewed_at,     ord.generated_by,     ord.gift_to,     ord.renewal_price_id,     ord.bypass,     ord.bypass_reason,     ord.reset_access,     ord.extend_access,     ord.pkg_id_fk,     ord.is_revoked,     dv.device_type,     CASE       WHEN ord.renewal_price_id > 0 THEN ord.renewal_price_id       ELSE ord.price_id     END AS price_id,     CASE       WHEN ord.renewal_price_id > 0 THEN p.duration       ELSE ord.duration     END AS duration,     CASE       WHEN ord.renewal_price_id > 0 THEN p.disconnect_time       ELSE ord.disconnect_time     END AS disconnect_time,     CASE       WHEN p.discount_rate > 0       AND p.discount_start <= CURDATE()       AND p.discount_end >= CURDATE() THEN p.balance - (p.balance * p.discount_rate / 100)       ELSE p.balance     END AS balance   FROM     orders as ord     JOIN devices as dv on ord.username = dv.username     JOIN prices as p ON price_id = p.id     AND (       (         ord.bundle_id IS NOT NULL         AND ord.bundle_id = p.bundle_id       )       OR (         ord.service_id IS NOT NULL         AND ord.service_id = p.service_id       )     )   WHERE     ord.pkg_id_fk IS NULL     AND ord.extended_duration = 0     AND ord.extend_access = 0     AND ord.is_paid = 1     AND ord.duration != 0     AND ord.is_renew = 1     AND ord.was_renewed = 0     AND dv.is_verified = 1     AND dv.is_banned = 0     AND ord.object_name IS NULL     AND (       (p.is_approved IN (1, 4))       OR (         p.is_approved = 2         AND dv.device_type = 'Mobile'       )       OR (         p.is_approved = 3         AND dv.device_type = 'STB'       )     )     AND DATE_ADD(ord.paid_on, INTERVAL ord.duration HOUR) < NOW()     AND DATE(       DATE_ADD(ord.paid_on, INTERVAL ord.duration HOUR)     ) BETWEEN DATE(NOW() - INTERVAL 15 DAY)     AND DATE(NOW())   GROUP BY     ord.username,     ord.service_id,     ord.bundle_id   order by     ord.paid_on desc ) UNION(     SELECT       ord.orderid,       ord.username,       ord.msisdn,       ord.order_type,       ord.bundle_id,       ord.service_id,       ord.object_name,       ord.app_id,       ord.is_paid,       ord.is_renew,       ord.is_test,       ord.extended_duration,       ord.created,       ord.paid_on,       ord.was_renewed,       ord.renew_orderid,       ord.renewed_at,       ord.generated_by,       ord.gift_to,       ord.renewal_price_id,       ord.bypass,       ord.bypass_reason,       ord.reset_access,       ord.extend_access,       ord.pkg_id_fk,       ord.is_revoked,       dv.device_type,       CASE         WHEN ord.renewal_price_id > 0 THEN ord.renewal_price_id         ELSE ord.price_id       END AS price_id,       CASE         WHEN ord.renewal_price_id > 0 THEN p.duration         ELSE ord.duration       END AS duration,       CASE         WHEN ord.renewal_price_id > 0 THEN p.disconnect_time         ELSE ord.disconnect_time       END AS disconnect_time,       CASE         WHEN p.discount_rate > 0         AND p.discount_start <= CURDATE()         AND p.discount_end >= CURDATE() THEN p.balance - (p.balance * p.discount_rate / 100)         ELSE p.balance       END AS balance     FROM       orders as ord       JOIN devices as dv on ord.username = dv.username       JOIN prices as p ON price_id = p.id       AND (         (           ord.bundle_id IS NOT NULL           AND ord.bundle_id = p.bundle_id         )         OR (           ord.service_id IS NOT NULL           AND ord.service_id = p.service_id         )       )     WHERE       ord.pkg_id_fk IS NULL       AND ord.extended_duration != 0       AND ord.extend_access = 0       AND ord.is_paid = 1       AND ord.duration != 0       AND ord.is_renew = 1       AND ord.was_renewed = 0       AND dv.is_verified = 1       AND dv.is_banned = 0       AND ord.object_name IS NULL       AND (         (p.is_approved IN (1, 4))         OR (           p.is_approved = 2           AND dv.device_type = 'Mobile'         )         OR (           p.is_approved = 3           AND dv.device_type = 'STB'         )       )       AND DATE_ADD(ord.paid_on, INTERVAL ord.extended_duration HOUR) < NOW()       AND DATE(         DATE_ADD(ord.paid_on, INTERVAL ord.extended_duration HOUR)       ) BETWEEN DATE(NOW() - INTERVAL 15 DAY)       AND DATE(NOW())     GROUP BY       ord.username,       ord.service_id,       ord.bundle_id     order by       ord.paid_on desc   ) UNION   (     SELECT       ord.orderid,       ord.username,       ord.msisdn,       ord.order_type,       ord.bundle_id,       ord.service_id,       ord.object_name,       ord.app_id,       ord.is_paid,       ord.is_renew,       ord.is_test,       ord.extended_duration,       ord.created,       ord.paid_on,       ord.was_renewed,       ord.renew_orderid,       ord.renewed_at,       ord.generated_by,       ord.gift_to,       ord.renewal_price_id,       ord.bypass,       ord.bypass_reason,       ord.reset_access,       ord.extend_access,       ord.pkg_id_fk,       ord.is_revoked,       ord.price_id,       ord.duration,       ord.disconnect_time,       ord.balance,       dv.device_type     FROM       orders as ord       JOIN devices as dv on ord.username = dv.username     WHERE       ord.pkg_id_fk IS NOT NULL       AND ord.extended_duration = 0       AND ord.extend_access = 0       AND ord.is_paid = 1       AND ord.duration != 0       AND ord.is_renew = 1       AND ord.was_renewed = 0       AND ord.object_name IS NULL       AND dv.is_verified = 1       AND dv.is_banned = 0       AND DATE_ADD(ord.paid_on, INTERVAL ord.duration HOUR) < NOW()       AND DATE(         DATE_ADD(ord.paid_on, INTERVAL ord.duration HOUR)       ) BETWEEN DATE(NOW() - INTERVAL 15 DAY)       AND DATE(NOW())     GROUP BY       ord.username,       ord.pkg_id_fk     order by       ord.paid_on desc   ) UNION(     SELECT       ord.orderid,       ord.username,       ord.msisdn,       ord.order_type,       ord.bundle_id,       ord.service_id,       ord.object_name,       ord.app_id,       ord.is_paid,       ord.is_renew,       ord.is_test,       ord.extended_duration,       ord.created,       ord.paid_on,       ord.was_renewed,       ord.renew_orderid,       ord.renewed_at,       ord.generated_by,       ord.gift_to,       ord.renewal_price_id,       ord.bypass,       ord.bypass_reason,       ord.reset_access,       ord.extend_access,       ord.pkg_id_fk,       ord.is_revoked,       ord.price_id,       ord.duration,       ord.disconnect_time,       ord.balance,       dv.device_type     FROM       orders as ord       JOIN devices as dv on ord.username = dv.username     WHERE       ord.pkg_id_fk IS NOT NULL       AND ord.extended_duration != 0       AND ord.extend_access = 0       AND ord.is_paid = 1       AND ord.duration != 0       AND ord.is_renew = 1       AND ord.was_renewed = 0       AND dv.is_verified = 1       AND dv.is_banned = 0       AND ord.object_name IS NULL       AND DATE_ADD(ord.paid_on, INTERVAL ord.extended_duration HOUR) < NOW()       AND DATE(         DATE_ADD(ord.paid_on, INTERVAL ord.extended_duration HOUR)       ) BETWEEN DATE(NOW() - INTERVAL 15 DAY)       AND DATE(NOW())     GROUP BY       ord.username,       ord.pkg_id_fk     order by       ord.paid_on desc   ) 

show create tables orders, devices, prices

orders | CREATE TABLE `orders` (   `orderid` varchar(50) COLLATE utf8_unicode_ci NOT NULL,   `username` varchar(128) COLLATE utf8_unicode_ci NOT NULL,   `msisdn` varchar(150) COLLATE utf8_unicode_ci NOT NULL,   `order_type` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'Subscription',   `bundle_id` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL,   `service_id` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,   `object_name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,   `app_id` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL,   `balance` float NOT NULL,   `is_paid` int(11) NOT NULL DEFAULT '0',   `is_renew` tinyint(1) NOT NULL DEFAULT '1',   `is_test` tinyint(1) NOT NULL DEFAULT '0',   `duration` int(11) NOT NULL DEFAULT '0',   `extended_duration` int(11) NOT NULL DEFAULT '0',   `disconnect_time` int(11) NOT NULL DEFAULT '0',   `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,   `paid_on` timestamp NULL DEFAULT NULL,   `was_renewed` tinyint(1) NOT NULL DEFAULT '0',   `renew_orderid` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,   `renewed_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',   `generated_by` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,   `gift_to` varchar(125) COLLATE utf8_unicode_ci DEFAULT NULL,   `price_id` int(11) NOT NULL DEFAULT '0',   `renewal_price_id` int(11) NOT NULL DEFAULT '0',   `bypass` tinyint(1) NOT NULL DEFAULT '0',   `bypass_reason` text COLLATE utf8_unicode_ci,   `reset_access` tinyint(1) NOT NULL DEFAULT '0',   `extend_access` tinyint(1) NOT NULL DEFAULT '0',   `pkg_id_fk` int(11) DEFAULT NULL,   `is_revoked` tinyint(4) NOT NULL DEFAULT '0',   PRIMARY KEY (`orderid`),   KEY `service_id_index` (`service_id`),   KEY `index_username` (`username`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci |   devices | CREATE TABLE `devices` (   `id` int(11) NOT NULL AUTO_INCREMENT,   `username` varchar(100) COLLATE utf8_unicode_ci NOT NULL,   `device_id` varchar(255) COLLATE utf8_unicode_ci NOT NULL,   `imei` varchar(200) COLLATE utf8_unicode_ci DEFAULT NULL,   `serial_number` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,   `mac_address` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,   `msisdn` varchar(150) COLLATE utf8_unicode_ci NOT NULL,   `auth` varchar(255) COLLATE utf8_unicode_ci NOT NULL,   `old_auth` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,   `device_type` varchar(50) COLLATE utf8_unicode_ci NOT NULL,   `device_os` varchar(50) COLLATE utf8_unicode_ci NOT NULL,   `device_osversion` varchar(50) COLLATE utf8_unicode_ci NOT NULL,   `device_appversion` varchar(50) COLLATE utf8_unicode_ci NOT NULL,   `device_pushtoken` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,   `language` varchar(50) COLLATE utf8_unicode_ci DEFAULT 'en',   `verification_code` varchar(10) COLLATE utf8_unicode_ci NOT NULL,   `is_verified` tinyint(4) NOT NULL DEFAULT '0',   `is_notification` tinyint(1) NOT NULL DEFAULT '1',   `is_refresh` tinyint(1) NOT NULL DEFAULT '0',   `is_test` tinyint(1) NOT NULL DEFAULT '0',   `is_banned` tinyint(1) NOT NULL DEFAULT '0',   `is_external` tinyint(1) NOT NULL DEFAULT '0',   `lastlogin` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',   `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,   PRIMARY KEY (`id`),   KEY `wtb_devices_idx_id_is_verified` (`is_verified`) ) ENGINE=InnoDB AUTO_INCREMENT=414709 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci    prices | CREATE TABLE `prices` (   `id` int(11) NOT NULL AUTO_INCREMENT,   `plan_model` tinyint(1) NOT NULL DEFAULT '0',   `service_id` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,   `bundle_id` int(11) DEFAULT NULL,   `balance` float NOT NULL,   `cost` float NOT NULL,   `costBundle` float NOT NULL,   `duration` int(11) NOT NULL,   `disconnect_time` int(11) NOT NULL DEFAULT '0',   `one_time` tinyint(1) NOT NULL DEFAULT '0',   `discount_rate` int(11) NOT NULL DEFAULT '0',   `discount_start` date DEFAULT NULL,   `discount_end` date DEFAULT NULL,   `is_approved` int(11) NOT NULL DEFAULT '0',   `is_renew` int(11) NOT NULL DEFAULT '1',   `double_play` varchar(50) COLLATE utf8_unicode_ci NOT NULL,   `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,   PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=2653 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci