I have an ugly query on MySQL. There are a lot of subqueries in the query. They can not change the query for now. Sometimes queries stack in statistics state. Some document says, it depends on your server optimizer_search_depth config parameter. I tried 0 and 1, but nothing is changed. The queries get still timeout.
MySQL version 8.0.20 on AWS RDS.
Here is the EXPLAIN result.
+--+-----------+-----+----------+------+-------+----+--------+----------------------------------+
|id|select_type|table|partitions|type |key_len|rows|filtered|Extra |
+--+-----------+-----+----------+------+-------+----+--------+----------------------------------+
|1 |PRIMARY |NULL |NULL |NULL |NULL |NULL|NULL |No tables used |
|45|SUBQUERY |td |NULL |ref |96 |48 |100 |NULL |
|45|SUBQUERY |ti |NULL |eq_ref|8 |1 |100 |Using where |
|45|SUBQUERY |c |NULL |ref |110 |1 |100 |Using index |
|43|SUBQUERY |NULL |NULL |NULL |NULL |NULL|NULL |Impossible WHERE |
|44|SUBQUERY |ti |NULL |ref |78 |3 |1.67 |Using where |
|44|SUBQUERY |td |NULL |ref |8 |1 |4.85 |Using where |
|42|SUBQUERY |td |NULL |ref |78 |2 |100 |Using index |
|42|SUBQUERY |ti |NULL |eq_ref|8 |1 |5 |Using where |
|41|SUBQUERY |td |NULL |ref |78 |10 |100 |Using index |
|41|SUBQUERY |ti |NULL |eq_ref|8 |1 |5 |Using where |
|40|SUBQUERY |td |NULL |ref |96 |48 |100 |NULL |
|40|SUBQUERY |ti |NULL |eq_ref|8 |1 |5 |Using where |
|39|SUBQUERY |ti |NULL |ref |387 |1 |5 |Using where |
|38|SUBQUERY |ti |NULL |ref |111 |1 |5 |Using where |
|37|SUBQUERY |ti |NULL |ref |111 |1 |100 |Using where |
|36|SUBQUERY |ti |NULL |ref |303 |49 |100 |Using where; Using index |
|36|SUBQUERY |c |NULL |ref |110 |1 |100 |Using index |
|35|SUBQUERY |ti |NULL |ref |78 |3 |100 |Using where; Using index |
|35|SUBQUERY |c |NULL |ref |110 |1 |100 |Using index |
|33|SUBQUERY |t |NULL |ref |752 |2 |2.5 |Using where |
|32|SUBQUERY |t |NULL |ref |752 |2 |5 |Using where |
|31|SUBQUERY |ti |NULL |ref |753 |10 |3.77 |Using where |
|30|SUBQUERY |td |NULL |ref |1203 |1 |100 |NULL |
|30|SUBQUERY |ti |NULL |eq_ref|8 |1 |100 |Using where |
|30|SUBQUERY |c |NULL |ref |110 |1 |100 |Using index |
|29|SUBQUERY |ti |NULL |range |159 |11 |0.45 |Using index condition; Using where|
|28|SUBQUERY |ti |NULL |range |159 |11 |0.45 |Using index condition; Using where|
|28|SUBQUERY |td |NULL |ref |8 |1 |100 |Using where |
|27|SUBQUERY |td |NULL |ref |414 |1 |100 |Using index |
|27|SUBQUERY |ti |NULL |eq_ref|8 |1 |5 |Using where |
|26|SUBQUERY |td |NULL |ref |414 |1 |100 |Using index |
|26|SUBQUERY |ti |NULL |eq_ref|8 |1 |5 |Using where |
|25|SUBQUERY |ti |NULL |ref |303 |14 |0.36 |Using where |
|25|SUBQUERY |td |NULL |ref |8 |1 |4.85 |Using where |
|24|SUBQUERY |ti |NULL |ref |303 |14 |0.36 |Using where |
|24|SUBQUERY |td |NULL |ref |8 |1 |4.85 |Using where |
|23|SUBQUERY |td |NULL |ref |189 |1 |100 |Using index |
|23|SUBQUERY |ti |NULL |eq_ref|8 |1 |5 |Using where |
|22|SUBQUERY |td |NULL |ref |189 |1 |100 |Using index |
|22|SUBQUERY |ti |NULL |eq_ref|8 |1 |5 |Using where |
|21|SUBQUERY |ti |NULL |range |84 |1 |100 |Using index condition; Using where|
|21|SUBQUERY |td |NULL |ref |8 |1 |4.85 |Using where |
|20|SUBQUERY |ti |NULL |range |84 |1 |100 |Using index condition; Using where|
|20|SUBQUERY |td |NULL |ref |8 |1 |4.85 |Using where |
|19|SUBQUERY |ti |NULL |ref |753 |10 |0.5 |Using index condition; Using where|
|19|SUBQUERY |td |NULL |ref |8 |1 |4.85 |Using where |
|18|SUBQUERY |ti |NULL |ref |753 |10 |0.5 |Using index condition; Using where|
|18|SUBQUERY |td |NULL |ref |8 |1 |4.85 |Using where |
|17|SUBQUERY |ti |NULL |range |462 |2 |2.5 |Using index condition; Using where|
|17|SUBQUERY |td |NULL |ref |8 |1 |4.85 |Using where |
|16|SUBQUERY |ti |NULL |range |84 |1 |10 |Using index condition; Using where|
|15|SUBQUERY |ti |NULL |range |912 |2 |2.5 |Using index condition; Using where|
|14|SUBQUERY |ti |NULL |ref |753 |10 |0.5 |Using index condition; Using where|
|13|SUBQUERY |ti |NULL |range |159 |11 |0.45 |Using index condition; Using where|
|13|SUBQUERY |td |NULL |ref |8 |1 |4.85 |Using where |
|12|SUBQUERY |ti |NULL |range |462 |2 |2.5 |Using index condition; Using where|
|11|SUBQUERY |ti |NULL |range |84 |1 |100 |Using index condition |
|10|SUBQUERY |ti |NULL |ref |303 |14 |0.36 |Using where |
|9 |SUBQUERY |td |NULL |ref |96 |48 |100 |NULL |
|9 |SUBQUERY |ti |NULL |eq_ref|8 |1 |5 |Using where |
|8 |SUBQUERY |ti |NULL |ref |753 |10 |50 |Using where |
|8 |SUBQUERY |td |NULL |ref |8 |1 |4.85 |Using where |
|7 |SUBQUERY |ti |NULL |ref |111 |1 |100 |Using index |
|7 |SUBQUERY |td |NULL |ref |8 |1 |4.85 |Using where |
|5 |SUBQUERY |ti |NULL |ref |387 |1 |50 |Using where |
|5 |SUBQUERY |c |NULL |ref |110 |1 |100 |Using index |
|4 |SUBQUERY |ti |NULL |ref |753 |10 |1.85 |Using where |
|3 |SUBQUERY |td |NULL |ref |78 |10 |100 |Using index |
|3 |SUBQUERY |ti |NULL |eq_ref|8 |1 |65.05 |Using where |
|3 |SUBQUERY |c |NULL |ref |110 |1 |100 |Using index |
|2 |SUBQUERY |ti |NULL |ref |78 |10 |100 |Using where; Using index |
|2 |SUBQUERY |td |NULL |ref |8 |1 |100 |Using index |
|2 |SUBQUERY |c |NULL |ref |110 |1 |100 |Using index |
+--+-----------+-----+----------+------+-------+----+--------+----------------------------------+
Query:
SELECT (SELECT IF(COUNT(c.PKChargebackId) > 0, '1', '0') AS RES FROM Chargeback c INNER JOIN TransactionItem ti ON c.OrderId = ti.OrderId INNER JOIN TransactionDetail td ON ti.PKTransactionItemId = td.FKTransactionItemId WHERE ti.SenderPhone = '000000') AS SameSenderPhoneHasChargeback,
(SELECT IF(COUNT(c.PKChargebackId) > 0, '1', '0') AS RES FROM Chargeback c INNER JOIN TransactionItem ti ON c.OrderId = ti.OrderId INNER JOIN TransactionDetail td ON ti.PKTransactionItemId = td.FKTransactionItemId WHERE ti.SenderPhone != td.ReceiverPhone AND td.ReceiverPhone = '0000000') AS SameReceiverPhoneHasChargeback,
(SELECT IF(COUNT(DISTINCT(ti.OrderId)) >= 5,'1','0') AS RES FROM TransactionItem ti WHERE ti.Email ='test@gmail.com' AND ti.OrderDate >= DATE_ADD(NOW(), INTERVAL -5 DAY)) AS LastFiveDayOrders,
(SELECT IF(COUNT(c.PKChargebackId) > 0, '1', '0') AS RES FROM Chargeback c INNER JOIN TransactionItem ti ON c.OrderId = ti.OrderId WHERE ti.HashedData='9445b81fcf6df59d129d519724a509d6' AND ti.IsSavedCreditCard=0 ) AS SameHashedHasChargeback,
(SELECT IF(80.32 <100, '1', '0') AS RES) AS FromSiteOrderTotal75CicekSepeti,
(SELECT (IF(SUM(td.Quantity) > 2, '1', '0')) as RES
FROM TransactionItem ti
JOIN TransactionDetail td ON ti.PKTransactionItemId = td.FKTransactionItemId
WHERE ti.OrderId = '152706596'
and td.VariantId in ('1505398819','1503264559','1503264497','1505418522','1503415399','1503333610')) AS CheckProduct,
(SELECT IF(COUNT(ti.OrderId) > 1, '1', '0') as RES
FROM TransactionItem ti
JOIN TransactionDetail td ON ti.PKTransactionItemId = td.FKTransactionItemId
WHERE ti.Email = 'test@gmail.com'
and ti.CreatedOn >= DATE_SUB(NOW(), INTERVAL 1 YEAR)
and td.VariantId in ('1505398819','1503264559','1503264497','1505418522','1503415399','1503333610')) AS CheckProduct,
(SELECT IF(COUNT(DISTINCT(ti.OrderId)) > 4,'1','0') AS RES FROM TransactionItem ti JOIN TransactionDetail td ON ti.PKTransactionItemId = td.FKTransactionItemId WHERE ti.OrderDate > date_add('2020-11-23 01:14', INTERVAL -60 MINUTE) AND td.Latitude = '40.22126' and td.Longitude = '28.9959') AS SameDaySameReceiverAddressXOrder,
(SELECT IF(COUNT(DISTINCT(ti.OrderId)) > 2,'1','0') AS RES FROM TransactionItem ti WHERE ti.SenderName ='test test' AND ti.OrderId != '152706596' AND ti.OrderDate between DATE_FORMAT(NOW(), '%Y-%m-%d 00:00:00') and DATE_FORMAT(NOW(), '%Y-%m-%d 23:59:59') ) AS SameDaySameSenderNameXOrder,
(SELECT IF(COUNT(DISTINCT(ti.OrderId)) > 2,'1','0') AS RES FROM TransactionItem ti WHERE ti.OrderDate > date_add('2020-11-23 01:14', INTERVAL -60 MINUTE) AND ti.SenderPhone ='0000000000') AS SameDaySameSenderPhoneAndMobileXOrder,
(SELECT IF(COUNT(DISTINCT(ti.OrderId)) > 2,'1','0') AS RES FROM TransactionItem ti WHERE ti.SenderName ='test test' AND ti.IpAddress = '1.1.1.1' AND ti.OrderId != '152706596' AND ti.OrderDate between DATE_FORMAT(NOW(), '%Y-%m-%d 00:00:00') and DATE_FORMAT(NOW(), '%Y-%m-%d 23:59:59') ) AS SameDaySameSenderNameAndIpXOrder,
(SELECT IF(COUNT(DISTINCT(ti.OrderId)) > 1,'1','0') AS RES FROM TransactionItem ti INNER JOIN TransactionDetail td ON td.FKTransactionItemId = ti.PKTransactionItemId WHERE ti.SenderName != td.ReceiverName AND td.ReceiverName ='test test' AND ti.IpAddress = '1.1.1.1' AND ti.OrderId != '152706596' AND ti.OrderDate between DATE_FORMAT(NOW(), '%Y-%m-%d 00:00:00') and DATE_FORMAT(NOW(), '%Y-%m-%d 23:59:59') ) AS SameDaySameReceiverNameAndIpXOrder,
(SELECT IF(COUNT(DISTINCT(ti.OrderId)) > 2,'1','0') AS RES FROM TransactionItem ti WHERE ti.Email ='test@gmail.com' AND ti.OrderId != '152706596' AND ti.OrderDate between DATE_FORMAT(NOW(), '%Y-%m-%d 00:00:00') and DATE_FORMAT(NOW(), '%Y-%m-%d 23:59:59') ) AS SameDaySameEmailXOrder,
(SELECT IF(COUNT(DISTINCT(ti.OrderId)) > 2,'1','0') AS RES FROM TransactionItem ti WHERE ti.Email ='test@gmail.com' AND ti.IpAddress = '1.1.1.1' AND ti.OrderId != '152706596' AND ti.OrderDate between DATE_FORMAT(NOW(), '%Y-%m-%d 00:00:00') and DATE_FORMAT(NOW(), '%Y-%m-%d 23:59:59') ) AS SameDaySameEmailAndIpXOrder,
(SELECT IF(COUNT(DISTINCT(ti.OrderId)) > 2,'1','0') AS RES FROM TransactionItem ti WHERE ti.SenderPhone ='000000000' AND ti.IpAddress = '1.1.1.1' AND ti.OrderId != '152706596' AND ti.OrderDate between DATE_FORMAT(NOW(), '%Y-%m-%d 00:00:00') and DATE_FORMAT(NOW(), '%Y-%m-%d 23:59:59') ) AS SameDaySameSenderPhoneAndMobileAndIpXOrder,
(SELECT IF(COUNT(DISTINCT(ti.OrderId)) > 1,'1','0') AS RES FROM TransactionItem ti INNER JOIN TransactionDetail td ON td.FKTransactionItemId = ti.PKTransactionItemId WHERE ti.SenderName = 'test test' AND td.ReceiverName = 'test test' AND ti.IpAddress = '1.1.1.1' AND ti.OrderId != '152706596' AND ti.OrderDate between DATE_FORMAT(NOW(), '%Y-%m-%d 00:00:00') and DATE_FORMAT(NOW(), '%Y-%m-%d 23:59:59') ) AS SameDaySameSenderNameAndReceiverNameAndIpXOrder,
(SELECT IF(COUNT(DISTINCT(ti.OrderId)) > 1,'1','0') AS RES FROM TransactionItem ti JOIN TransactionDetail td ON ti.PKTransactionItemId = td.FKTransactionItemId WHERE ti.Email ='test@gmail.com' AND ti.OrderId != '152706596' AND td.VariantId = '1502831698' AND ti.OrderDate between DATE_FORMAT(NOW(), '%Y-%m-%d 00:00:00') and DATE_FORMAT(NOW(), '%Y-%m-%d 23:59:59')) AS SameDaySameEmailDiffOrderSameProduct,
(SELECT IF(COUNT(DISTINCT(ti.OrderId)) > 1,'1','0') AS RES FROM TransactionItem ti JOIN TransactionDetail td ON ti.PKTransactionItemId = td.FKTransactionItemId WHERE ti.Email ='test@gmail.com' AND ti.OrderId != '152706596' AND td.VariantId = '1503661868' AND ti.OrderDate between DATE_FORMAT(NOW(), '%Y-%m-%d 00:00:00') and DATE_FORMAT(NOW(), '%Y-%m-%d 23:59:59')) AS SameDaySameEmailDiffOrderSameProduct,
(SELECT IF(COUNT(DISTINCT(ti.OrderId)) > 1,'1','0') AS RES FROM TransactionItem ti JOIN TransactionDetail td ON ti.PKTransactionItemId = td.FKTransactionItemId WHERE ti.SenderPhone ='000000000' AND ti.OrderId != '152706596' AND td.VariantId = '1502831698' AND ti.OrderDate between DATE_FORMAT(NOW(), '%Y-%m-%d 00:00:00') and DATE_FORMAT(NOW(), '%Y-%m-%d 23:59:59')) AS SameDaySameSenderPhoneDiffOrderSameProduct,
(SELECT IF(COUNT(DISTINCT(ti.OrderId)) > 1,'1','0') AS RES FROM TransactionItem ti JOIN TransactionDetail td ON ti.PKTransactionItemId = td.FKTransactionItemId WHERE ti.SenderPhone ='0000000000' AND ti.OrderId != '152706596' AND td.VariantId = '1503661868' AND ti.OrderDate between DATE_FORMAT(NOW(), '%Y-%m-%d 00:00:00') and DATE_FORMAT(NOW(), '%Y-%m-%d 23:59:59')) AS SameDaySameSenderPhoneDiffOrderSameProduct,
(SELECT IF(COUNT(DISTINCT(ti.OrderId)) > 1,'1','0') AS RES FROM TransactionItem ti JOIN TransactionDetail td ON ti.PKTransactionItemId = td.FKTransactionItemId WHERE ti.SenderPhone != td.ReceiverPhone AND td.ReceiverPhone ='00000000000' AND ti.OrderId != '152706596' AND td.VariantId = '1502831698' AND ti.OrderDate between DATE_FORMAT(NOW(), '%Y-%m-%d 00:00:00') and DATE_FORMAT(NOW(), '%Y-%m-%d 23:59:59')) AS SameDaySameReceiverPhoneDiffOrderSameProduct,
(SELECT IF(COUNT(DISTINCT(ti.OrderId)) > 1,'1','0') AS RES FROM TransactionItem ti JOIN TransactionDetail td ON ti.PKTransactionItemId = td.FKTransactionItemId WHERE ti.SenderPhone != td.ReceiverPhone AND td.ReceiverPhone ='00000000000' AND ti.OrderId != '152706596' AND td.VariantId = '1503661868' AND ti.OrderDate between DATE_FORMAT(NOW(), '%Y-%m-%d 00:00:00') and DATE_FORMAT(NOW(), '%Y-%m-%d 23:59:59')) AS SameDaySameReceiverPhoneDiffOrderSameProduct,
(SELECT IF(COUNT(DISTINCT(ti.OrderId)) > 1,'1','0') AS RES FROM TransactionItem ti JOIN TransactionDetail td ON ti.PKTransactionItemId = td.FKTransactionItemId WHERE ti.SenderName = 'test test' AND ti.OrderId != '152706596' AND td.VariantId = '1502831698' AND ti.OrderDate between DATE_FORMAT(NOW(), '%Y-%m-%d 00:00:00') and DATE_FORMAT(NOW(), '%Y-%m-%d 23:59:59')) AS SameDaySameSenderNameDiffOrderSameProduct,
(SELECT IF(COUNT(DISTINCT(ti.OrderId)) > 1,'1','0') AS RES FROM TransactionItem ti JOIN TransactionDetail td ON ti.PKTransactionItemId = td.FKTransactionItemId WHERE ti.SenderName = 'test test' AND ti.OrderId != '152706596' AND td.VariantId = '1503661868' AND ti.OrderDate between DATE_FORMAT(NOW(), '%Y-%m-%d 00:00:00') and DATE_FORMAT(NOW(), '%Y-%m-%d 23:59:59')) AS SameDaySameSenderNameDiffOrderSameProduct,
(SELECT IF(COUNT(DISTINCT(ti.OrderId)) > 1,'1','0') AS RES FROM TransactionItem ti JOIN TransactionDetail td ON ti.PKTransactionItemId = td.FKTransactionItemId WHERE ti.SenderName != td.ReceiverName AND td.ReceiverName = 'test test' AND ti.OrderId != '152706596' AND td.VariantId = '1502831698' AND ti.OrderDate between DATE_FORMAT(NOW(), '%Y-%m-%d 00:00:00') and DATE_FORMAT(NOW(), '%Y-%m-%d 23:59:59')) AS SameDaySameReceiverNameDiffOrderSameProduct,
(SELECT IF(COUNT(DISTINCT(ti.OrderId)) > 1,'1','0') AS RES FROM TransactionItem ti JOIN TransactionDetail td ON ti.PKTransactionItemId = td.FKTransactionItemId WHERE ti.SenderName != td.ReceiverName AND td.ReceiverName = 'test test' AND ti.OrderId != '152706596' AND td.VariantId = '1503661868' AND ti.OrderDate between DATE_FORMAT(NOW(), '%Y-%m-%d 00:00:00') and DATE_FORMAT(NOW(), '%Y-%m-%d 23:59:59')) AS SameDaySameReceiverNameDiffOrderSameProduct,
(SELECT IF(COUNT(DISTINCT(td.ReceiverName)) > 6, '2', '0') AS RES FROM TransactionItem ti INNER JOIN TransactionDetail td ON td.FKTransactionItemId = ti.PKTransactionItemId WHERE ti.IpAddress = '1.1.1.1' AND ti.SenderName != td.ReceiverName AND td.ReceiverName != 'test test' AND ti.OrderId != '152706596' AND ti.OrderDate between DATE_FORMAT(NOW(), '%Y-%m-%d 00:00:00') and DATE_FORMAT(NOW(), '%Y-%m-%d 23:59:59')) AS SameDaySameIPAddressDifferentReceiverName,
(SELECT IF(COUNT(DISTINCT(ti.SenderName)) > 6, '2', '0') AS RES FROM TransactionItem ti WHERE ti.IpAddress = '1.1.1.1' AND ti.SenderName != 'test test' AND ti.OrderId != '152706596' AND ti.OrderDate between DATE_FORMAT(NOW(), '%Y-%m-%d 00:00:00') and DATE_FORMAT(NOW(), '%Y-%m-%d 23:59:59')) AS SameDaySameIPAddressDifferentSenderName,
(SELECT IF(COUNT(c.PKChargebackId) > 0, '1', '0') AS RES FROM Chargeback c INNER JOIN TransactionItem ti ON c.OrderId = ti.OrderId INNER JOIN TransactionDetail td ON ti.PKTransactionItemId = td.FKTransactionItemId WHERE td.ReceiverAddress = 'test') AS SameReceiverAddressHasChargeback,
(SELECT IF(COUNT(DISTINCT(ti.OrderId)) >= 2 AND SUM(DISTINCT ti.OrderPrice) > 650, '1', '0') AS RES FROM TransactionItem ti WHERE ti.Email = 'test@gmail.com' AND ti.OrderDate BETWEEN DATE_ADD(NOW(), INTERVAL -10 DAY) AND DATE_ADD(NOW(), INTERVAL 1 DAY)) AS PastXDayMoreThanYOrdersTotalAmountBiggerYCicek,
(SELECT IF(COUNT(DISTINCT(t.OrderId)) > 4, '1', '0') AS RES FROM Transaction t WHERE t.Email = 'test@gmail.com' AND t.OrderId != '152706596' AND t.IsPaid = 0 AND t.ApplicationId = '9c3d2993-8be4-4af0-9cfb-4d0dcd8db549' AND DATE_FORMAT(t.OrderDate,'%d-%m-%Y') = DATE_FORMAT(NOW(),'%d-%m-%Y')) AS HasNotBeenPaidXOldTransactionCicekSepeti,
(SELECT IF(COUNT(DISTINCT(t.OrderId)) > 0 AND t.OrderPrice > 300, '1', '0') AS RES FROM Transaction t WHERE t.Email = 'test@gmail.com' AND t.OrderId != '152706596' AND t.IsPaid = 0 AND t.ApplicationId = '9c3d2993-8be4-4af0-9cfb-4d0dcd8db549' AND t.OrderDate BETWEEN DATE_ADD(NOW(), INTERVAL -90 DAY) AND DATE_ADD(NOW(), INTERVAL 1 DAY)) AS HasNotBeenPaidXOldTransactionsTotalAmountGtYCicekS,
(SELECT IF(80.32 >= 300 AND 80.32 <= 499, '1', '0') AS RES) AS FromSiteOrderTotal150_250CicekSepeti,
(SELECT IF(COUNT(c.PKChargebackId) > 0, '1', '0') AS RES FROM Chargeback c INNER JOIN TransactionItem ti ON c.OrderId = ti.OrderId WHERE ti.SenderPhone = '0000000' AND '00000000' != '00000000') AS SameSenderPhoneByReceiverPhoneHasChargeback,
(SELECT IF(COUNT(c.PKChargebackId) > 0, '1', '0') AS RES FROM Chargeback c INNER JOIN TransactionItem ti ON c.OrderId = ti.OrderId WHERE ti.SenderName = 'test test' AND 'test test' != 'test test') AS SameSenderNameByReceiverNameHasChargeback,
(SELECT IF(COUNT(DISTINCT(ti.HashedData)) > 2,'1','0') AS RES FROM TransactionItem ti WHERE ti.HashedData not in ('9445b81fcf6df59d129d519724a509d6','9445b81fcf6df59d129d519724a509d6') AND ti.OrderId = '152706596') AS SameOrderDiffHashedDataXOrder,
(SELECT IF(COUNT(ti.HashedData) > 3,'1','0') AS RES FROM TransactionItem ti WHERE ti.HashedData in ('9445b81fcf6df59d129d519724a509d6','9445b81fcf6df59d129d519724a509d6') AND ti.OrderId = '152706596') AS SameOrderSameHashedDataXOrder,
(SELECT IF(COUNT(DISTINCT(ti.OrderId)) >= 3,'1','0') AS RES FROM TransactionItem ti WHERE ti.HashedData ='9445b81fcf6df59d129d519724a509d6' AND ti.IsSavedCreditCard=0 AND ti.OrderDate > DATE_ADD(NOW(), INTERVAL -10 DAY) and ti.OrderDate < DATE_ADD(NOW(), INTERVAL 1 DAY)) AS SameHashedDataV2Last10DayXOrder,
(SELECT IF(COUNT(DISTINCT(ti.OrderId)) > 4,'1','0') FROM TransactionDetail td join TransactionItem ti on td.FKTransactionItemId = ti.PKTransactionItemId where ti.CreatedOn > date_add('2020-11-23 01:14',INTERVAL -10 MINUTE) and td.Latitude = '40.22126' and td.Longitude = '28.9959') AS CheckLatitudeAndLongitude,
(SELECT IF(COUNT(DISTINCT(ti.OrderId)) > 3,'1','0') FROM TransactionDetail td join TransactionItem ti on td.FKTransactionItemId = ti.PKTransactionItemId where ti.CreatedOn > date_add('2020-11-23 01:14',INTERVAL -60 MINUTE) and td.ReceiverPhone = '00000000') AS CheckReceiverPhone,
(SELECT IF(COUNT(DISTINCT(ti.OrderId)) > 2,'1','0') FROM TransactionDetail td join TransactionItem ti on td.FKTransactionItemId = ti.PKTransactionItemId where ti.CreatedOn > date_add('2020-11-23 01:14',INTERVAL -60 MINUTE) and td.ReceiverPhone = '000000000') AS CheckReceiverAndSenderPhone,
(select IF(COUNT(DISTINCT(ti.OrderId)) >= 1,'1','0') from TransactionItem ti join TransactionDetail td on ti.PKTransactionItemId = td.FKTransactionItemId where ti.CreatedOn > date_add('2020-11-23 01:14', INTERVAL -1 DAY) and ti.OrderId !='152706596' and td.ReceiverPhone = '000000000' and 1 = ( select IF(count(DISTINCT(ti.OrderId)) > 1, 1 ,0) from TransactionItem ti join TransactionDetail td on ti.PKTransactionItemId = td.FKTransactionItemId and ti.SenderPhone = td.ReceiverPhone where ti.CreatedOn > date_add('2020-11-23 01:14', INTERVAL -1 DAY) and td.ReceiverPhone = '0000000000') ) AS CheckSenderAndReceiverPhone,
(SELECT IF(COUNT(c.PKChargebackId) > 0, '1', '0') AS RES FROM Chargeback c INNER JOIN TransactionItem ti ON c.OrderId = ti.OrderId INNER JOIN TransactionDetail td ON ti.PKTransactionItemId = td.FKTransactionItemId WHERE td.Latitude = '40.22126' and td.Longitude = '28.9959') AS SameReceiverNameHasChargeback
To @Rick James
This database is not well designed. In addition to the Latitude/Longitude, the OrderId column is also VARCHAR rather than INT.
AND '00000000' != '00000000'
Actually, there are two different phone number or customer name here. Normally, developers should be doing on the app side, they don't but they will change.
I create indexes that you suggested and drop the unique keys and I deleted some indexes according to INDEX(a,b) - INDEX(a) rule.
I know the using function on the condition is wrong, we changed it like this ti.OrderDate between DATE_FORMAT(NOW(), '%Y-%m-%d 00:00:00') and DATE_FORMAT(NOW(), '%Y-%m-%d 23:59:59') is it still wrong or just longer than your suggest?
These transaction tables are approximately 50M rows. The server has 64GB memory and innodb_buffer_pool_size is 44GB.
Best Answer
Latitude/longitude -- VARCHAR?? Why not FLOAT (or other numeric value).
The use of
=will happen to work if you have exactly the same value and are usingVARCHAR. But this is highly unusual.What's this?
INDEXsuggestions:Instead of
do
(
EXISTS(..)quits when the first occurrence is found, hence more efficient.COUNT()has to find how many.COUNT(DISTINCT ..)needs a de-dup pass.)(There is no shortcut for
COUNT(DISTINCT(ti.OrderId)) >= 5.)Not sargeable:
Change it to
How much RAM do you have? Please provide
SHOW TABLE STATUS. What is the value ofinnodb_buffer_pool_size? UUIDs are especially inefficient for large tables; I want to see if we should take some special effort to improve their performance.A
PRIMARY KEYis aUNIQUEkey. That is,PRIMARY KEY(x), UNIQUE(x)is redundant; drop the unique.When you have
INDEX(a,b), don't also haveINDEX(a). (This recommendation does not apply tob, only to the leftmost column(s).)