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