Description:
I have the following query that sometimes returns an **empty set** on the master but NEVER on the read replica and there is data that is there that match on both databases. It is random and am wondering if there is a mysql setting or something with query cache. Running mysql 5.6.40-log on rds.
I have tried doing `optimizer_switch="index_merge_intersection=off"` but it didn't work.
Once I do optimize table `phppos_items` it fixes it for a little bit (3 minutes) and then it goes back to being erratic (mostly empty sets). These are all innodb tables.
settings:
https://gist.github.com/blasto333/82b18ef979438b93e4c39624bbf489d7
Seems to return empty set more often during busy time of day. Server is rds m4.large with 500 databases with 100 tables each
Query:
SELECT SUM( phppos_sales_items.damaged_qty ) AS damaged_qty,
SUM( phppos_sales_items.subtotal ) AS subtotal,
SUM( phppos_sales_items.total ) AS total,
SUM( phppos_sales_items.tax ) AS tax,
SUM( phppos_sales_items.profit ) AS profit
FROM `phppos_sales`
JOIN `phppos_sales_items` ON `phppos_sales_items`.`sale_id` = `phppos_sales`.`sale_id`
JOIN `phppos_items` ON `phppos_sales_items`.`item_id` = `phppos_items`.`item_id`
WHERE `phppos_sales`.`deleted` =0
AND `sale_time` BETWEEN '2019-01-01 00:00:00' AND '2019-12-31 23:59:59'
AND `phppos_sales`.`location_id` IN ( 1 )
AND `phppos_sales`.`store_account_payment` =0
AND `suspended` <2
AND `phppos_items`.`deleted` =0
AND `phppos_items`.`supplier_id` = '485'
GROUP BY `phppos_sales_items`.`sale_id`
Explain:
+----+-------------+--------------------+-------------+-----------------------------------------------------------------------------------------------+-----------------------------+---------+-------------------------------------------------------+------+---------------------------------------------------------------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------------------+-------------+-----------------------------------------------------------------------------------------------+-----------------------------+---------+-------------------------------------------------------+------+---------------------------------------------------------------------------------------------------------+
| 1 | SIMPLE | phppos_items | index_merge | PRIMARY,phppos_items_ibfk_1,deleted,deleted_system_item | phppos_items_ibfk_1,deleted | 5,4 | NULL | 44 | Using intersect(phppos_items_ibfk_1,deleted); Using where; Using index; Using temporary; Using filesort |
| 1 | SIMPLE | phppos_sales_items | ref | PRIMARY,item_id,phppos_sales_items_ibfk_3,phppos_sales_items_ibfk_4,phppos_sales_items_ibfk_5 | item_id | 4 | phppoint_customer.phppos_items.item_id | 16 | NULL |
| 1 | SIMPLE | phppos_sales | eq_ref | PRIMARY,deleted,location_id,sales_search,phppos_sales_ibfk_10 | PRIMARY | 4 | phppoint_customer.phppos_sales_items.sale_id | 1 | Using where |
+----+-------------+--------------------+-------------+-----------------------------------------------------------------------------------------------+-----------------------------+---------+-------------------------------------------------------+------+---------------------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)
How to repeat:
This is hard to reproduce but happens under load of the database.